Please help with formula 3

[CRuser89]

Hello,

I have a formula that I am concatenating:

{Command.ADDRESS}+' '+{Command.CITY}+' '+{Command.STATE}+' '+{Command.ZIP}

If one of the fields are blank, nothing shows up on the report for this field. If they all have data, then the field shows up fine.

Can you please let me know how to fix this? I want data to show up even if one or more fields is null.

Thanks....

 

[TheBlondOne]

you need to create a formula which covers the null values.

You can do something like this

whileprintingrecords;

stringvar add;
stringvar city;
stringvar state;
stringvar zip;
stringvar disp;

if isnull({address}) or {address} = "" then add:= "" else add:= {address} + " ";

if isnull({city}) or {city} = "" then city:="" else city:= {city} + " ";

repeat this for state and zip then at the bottom you can do

disp:= add + city + state + zip;

or and easier what is to put the fields into a text box and right click the text box, under the common tab, select suppress blank lines

hth

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[TheBlondOne]

sorry typo
should have read

or an easier whay is to put the fields into a text box and right click the text box, under the common tab, select suppress blank lines

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[BruceBussell]

Whenever there is a Null in any of the fields in a Crystal Formula it will not print the formula.

You will need to decide how you are are going to handle database NULLs in your Crystal Reports.

There are Check Box Flags
= Convert Database NULL Values to Default:
= Convert Other NULL Values to Default: (Like parameters in CR9 and later)

- in the File / Report Options menu -- to change the options for the Current Report

- and in the File / Options / Reporting menu -- to change the options for the Crystal Reports Editor and any New Reports.

You can check these boxes and your formulas will print "" where Nulls appear in a field, but the formula will still print.

However:

(a) If you choose to leave one or both of these NULL boxes unchecked, then you will need to use Null Checks like:

If IsNull({City}).... (Then do something)

or If Not IsNull({City}).... (Then do something)

in the first section of each Formula and Selection Filter where Nulls may be encountered. Null Checks must come before other Formula sections.

{b) If you choose to set one or both of these NULL boxes as checked, then:

To Check in a Crystal formula if Converted Null fields are blank you need to see if:
String Fields will = ""
Numeric Fields will = 0
Date Fields require special syntax --
ToText(DateField) = ""

Using Null Checks or Conerted Null Field Checks you can make a set of small Address, City, State, Zip formulas that can be combined into a final print address formula that looks good in most any case, whether some fields are Null at times or not.

 

[kskid]

Here's what I do

StringVar addr := "";

If IsNull({Command.Address}) or trim({Command.Address)) = "" Then
    addr := addr
Else
    addr := addr + {Command.ADDRESSES);

If IsNull({Command.City}) or trim({Command.City}) = "" Then
    addr := addr
Else
    addr := addr + " " +{Command.City);

If IsNull({Command.State}) or trim({Command.State}) = "" Then
    addr := addr
Else
    addr := addr + ", " +{Command.State);

If IsNull({Command.Zip}) or trim({Command.Zip}) = "" Then
    addr := addr
Else
    addr := addr + " " +{Command.Zip);

If trim(addr) = "" Then
    "No address listed..."
Else
    trim(ADDR)

 

[CRuser89]

Thanks everyone for your quick response. They're all good solutions...I decided to go with the easy one - adding a text box and inserting the fields in it....

Thanks again!