concatenated field city,state zip - when city empty state and zip don't how up

[bkel]

I created a concatenated field including city, state and zip - It looks great but when the city field is empty but data in the state and zip - the entire calculated field is blanks??? how do I get it to show the state and zip at least if there is one??? Region={city}&", "&{state}&" "&{zip}

thanks,
bkel

 

[pmax9999]

Try:

If 	Isnull({city}) 
Then 	{state} & " " & {zip}
Else	{city} & ", " & {state} & " " & {zip}

If either of the other 2 fields can be null you should test them for nulls as well.

Cheers
Pete

 

[bkel]

That seems odd that you have to do this - In Access if it is empty just what is there shows up -

Any other way to do this to handle ANY of the fields being empty?????

if not can you show the Multiple else if code??
thanks,
bkel

 

[pmax9999]

It is just the way Crystal deals with nulls.

You could change the formula result from "Exception for Nulls" to "Default Value for Nulls", and it woud return empty strings instead, but the disadvantage of this is that in the event of a Null {City}, the formula would return a string that starts with a comma and a space, eg ", ". Personally, I wouldn't accept that - your call.

My approach would be to leave the "Exception for Nulls", and write the formula as follows:

WhilePrintingRecords;
Local StringVar REGION := '';

If      Isnull({Table.City}) 
Then    REGION := ''
Else    REGION := {Table.City} + ', ';

If      Isnull({Table.State}) 
Then    REGION := REGION
Else    REGION := REGION + {Table.State} + ', ';

If      Isnull({Table.ZIP}) 
Then    REGION := REGION 
Else    REGION := REGION + {Table.Zip};

If      Right(REGION,2) = ', '
Then    Left(REGION, Len(REGION)-2)
Else    REGION

This will only put commas where they are required regardless of how many of the 3 fields are Null.


Cheers
Pete.

 

[AndyMc]

Try this - it should take care of any combination of blanks or nulls:

stringvar stateZip := trim({yourStatefield} & " " & {yourZipfield});
stringvar city := trim({yourCityfield});
If city = "" then
stateZip
else if stateZip = "" then
city
else
city & ", " & stateZip