Null Values in Concatenation

[rhammond]

Here's my problem:
I have 3 fields I'm trying to concatenate in a formula field.
My syntax: {field1} + ", " + {field2} + ", " + {field3}
However, there are some cases where Field 2 or Field 3 are null, but I still would want whatever wasn't null to appear.

Any ideas?

 

[synapsevampire]

You have a few options here.

The simplest is to use the File->Report Options->Convert Null Value to Default to eliminate nulls from appearing.

Or you can check for the existence of Nulls using the isnull() function, so something like:

whileprintingrecords;
stringvar Fld1 := "";
stringvar Fld2 := "";
stringvar Fld3 := "";

If isnull({Customer.Customer Name}) then
Fld1 := ""
else
Fld1 := {Customer.Customer Name};

If isnull({Customer.Contact Title}) then
Fld2 := ""
else
Fld2 := {Customer.Contact Title};

If isnull({Customer.Country}) then
Fld3 := ""
else
Fld3 := {Customer.Country};

Fld1+", "+fld2+", "+fld3

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rhammond]

K -

Your formula worked well (for some reason, despite checking off the Null qualification in the Report Options, it seemed to have no effect), but now my problem is that I can't use my formula field as a Group By field? Any ideas why that would be?

 

[IdoMillet]

Remove the
---------------------
whileprintingrecords;
---------------------

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[rhammond]

Thanks Ido...that did the trick (even though I spent the afternoon driving myself bonkers, going through Crystal's website - ack!)

Cheers,
Rebecca