If, and, then Formula Help 2

[livingstong713]

I am using Crystal Reports XI

Our address formats could have any number of fields missing data, i.e. there could be a City and State, but no Zip or the City and State could both be entered into the City field leaving the State field empty. I have started a formula that has been working, but of course there is always that one off that throws a wrench in the whole formula. I have mapped this formula to to insure that I have covered all bases, but I can not get it to populate for one particular customer that does not have a zip code. Here is my formula thus far:

if isnull({SHIPTO.City}) and isnull({SHIPTO.State})
and isnull ({SHIPTO.ZIP})
then ({SHIPTO.Country})
else if isnull({SHIPTO.City}) and isnull({SHIPTO.State})
then {SHIPTO.ZIP}
else if isnull ({SHIPTO.ZIP}) then {SHIPTO.City} + ', '+ {SHIPTO.State}
else if isnull({SHIPTO.City}) and not isnull({SHIPTO.State}) then {SHIPTO.State} + {SHIPTO.ZIP}
else {SHIPTO.City} + ', '+ {SHIPTO.State}+ ' ' +{SHIPTO.ZIP}

So, in a sense I want it to return whatever value is there and suppress what is not, but keep it in a nice format.

Any help is greatly appreciated.

Thanks in advance,
Genie

 

[AndyMc]

stringvar stateZip := trim({SHIPTO.State} & " " & {SHIPTO.ZIP});
stringvar city := trim({SHIPTO.City});

If city = "" then
stateZip
else if stateZip = "" then
city
else
city & ", " & stateZip

Andy

 

[livingstong713]

Andy,

Thanks, but your suggestion only works if all fields have data. It didn't work when the zip was missing.

Genie

 

[CRCRCR]

If you place all your fields into a Textbox, then there is the option of "Suppress Embedded Field BlankLines"

I think this may help you, without the need for formulae.

k.

 

[crystalkiwibruce]

I agree with CRCRCR. Use a text box with "Supress Embedded Fields" as the easiest way to create a address object with all the subsequent fields, and blank lines excluded.

However, if you want to know why your formula didn't work, try changing the report option (Convert NULL to default). If any of the fields in your formula are NULL, they will make the formula result NULL. Using that Report Option stops that happening.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[livingstong713]

Thank you all for your assistance! I love it when it turns out to be something simple. I am a new user to CR and I think I really over complicate things.

CRCRCR - Your suggestion worked perfectly. The items were already in a text box so the change was very simple.

crystalkiwibruce - I also took your suggestion and will make that change in all of the forms where I have formula using NULL.

Thank you again!
Genie