Stringing fields that can have null values 1

[TimothyP]

My input table looks like this:

Name Street_Num Street_Prefix Street_Name Street_Suffix
Robert 234 N Elm St
Bill 125 Ranchway Ct
John 44 Oak Blvd
Tim 2365 S Watters Ave

I am trying to create a formula to string together Street_Num, Street_Prefix, Street_Name, and Street_Suffix.

The problem I am encountering is when Street_Prefix is null the formula returns a null value for my entire string formula in the report.

Any suggestions?

Thanks,
Tim

 

[synapsevampire]

Try:

if isnull({table.streetnum}) then
""
else
{table.streetnum}
&
if isnull({table.streetprefix}) then
""
else
{table.streetprefix}
&
... you get the idea...

Another approach is to select File->Report Options and turn on the convert nulls to default values, which will eliminate all nulls from the report.

-k

 

[synapsevampire]

On second thought, you'll probably need spaces as well:

if isnull({table.streetnum}) then
""
else
{table.streetnum}+" "
&
if isnull({table.streetprefix}) then
""
else
{table.streetprefix}+" "
&
...

-k

 

[kskid]

The following is what I use for my address

StringVar addr := "";

If IsNull({ADDRESSES.STREET_NUMBER}) Then
    addr := addr
Else
    addr := addr + ToText({ADDRESSES.STREET_NUMBER});

If IsNull({ADDRESSES.STREET_PREFIX}) Then
    addr := addr
Else
    addr := TrimLeft(addr +" "+{ADDRESSES.STREET_PREFIX});

If IsNull({ADDRESSES.STREET_NAME}) Then
    addr := addr
Else
    addr := TrimLeft(addr + " " + {ADDRESSES.STREET_NAME});

If IsNull({ADDRESSES.STREET_SUFFIX}) Then
    addr := addr 
Else
    addr := TrimLeft(addr +" "+{ADDRESSES.STREET_SUFFIX});

//The following is for Apartment number
if isnull({ADDRESSES.SUB_NUMBER}) then
    addr := addr
else addr := addr + " #" + {ADDRESSES.SUB_NUMBER};

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

 

[ncampos]

I select File->Report Options and turn on the convert DATABASE nulls values to default and it works