Address String returns blank rows

[hermantb]

I have Account and Address tables linked on AddressID. If I structure a report to show the Account, Address1, City, State, Zip as individual fields then each account row indeed shows the corresponding address info. I merged those fileds into a formula as follows:
{ADDRESS.ADDRESS1}&", "&{ADDRESS.ADDRESS2}&", "&{ADDRESS.CITY}&", "&{ADDRESS.STATE}&", "&{ADDRESS.POSTALCODE}

This results in only about 20% of accounts having the address string. Why isn't the data filling in on all accounts?

Any ideas are appreciated.

 

[goranm]

Hi !

Probably it is because one ore more of the fields includes a NULL value.

Then the whole formula become empty.

Maybe your address2 is not filled every time.

/Goran

 

[hermantb]

Thanks...You information was helpful and I didn't know one null field would hide the whole row. In case anyone has this issue again here is what I got to work:

dim add1 as string
dim add2 as string
dim city as string
dim state as string
dim zip as string

If isnull({ADDRESS.ADDRESS1})= True then add1 = "." else add1 = {ADDRESS.ADDRESS1}
If isnull({ADDRESS.ADDRESS2}) = True then add2 = "." else add2 = {ADDRESS.ADDRESS2}
If isnull({ADDRESS.CITY}) = True then city = "." else city = {ADDRESS.CITY}
If isnull({ADDRESS.STATE}) = True then state = "." else state = {ADDRESS.STATE}
If isnull({ADDRESS.POSTALCODE}) = True then zip = "." else zip = {ADDRESS.POSTALCODE}

formula = add1&", "&add2&", "&city&", "&state&", "&zip

 

[goranm]

Hi !

If you don´t want the punctation you can just leave it like
If isnull({ADDRESS.ADDRESS1})= True then add1 = "" else add1 = {ADDRESS.ADDRESS1}


/Goran

 

[synapsevampire]

Simpler yet, use the File->Report Options->Convert null value to default.

Now you'll never get any nulls.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com