If value NULL formula blank 2

[CrystaLv]

I used formula
{table.city}& ", " &{table.state}& ", " &{table.zipcode}

But if any of those are NULL formula will not show nothing. How should I go about it?

Tahnks

 

[jdemmi]

Try....

{table.city}& ", " &{table.state}& ", " &ToText({table.zipcode})

-- Jason
"It's Just Ones and Zeros

 

[synapsevampire]

You've 2 options here, one is to set the File->Report Options->Convert null values to default.

The other is to think this through, in that if you're missing a city or a state, it's probably a bad idea to just display what you have, you need to flag them as bad.

So try:

if isnull({table.city}) or isnull({table.state}) then
"bad address"
else
if isnull({table.zip}) then
{table.city} & ", " & {table.state}
else
{table.city} & ", " & {table.state} & " " {table.zip}

Anyway, you get the idea, you can just change the bad address to reflect what you have if it doesn't matter if you have bad addresses.

-k

 

[CrystaLv]

See it is not nessesarily bad - it could be international like Tokyo, (zipNull) Japan. This is why I want to instead of having a field per each - get them into formula so formatting will look nice even if something missing. But I think I got your approach, synapsevampire.

thanks to all

 

[CrystaLv]

I made it into

if isnull({t.CITY}) then {t.STATE} & ", " & {t.POSTAL_CODE}
else
if isnull({t.STATE}) then {t.CITY} & ", " & {t.POSTAL_CODE}
else
if isnull({t.POSTAL_CODE}) then {t.CITY} & ", " & {t.STATE}
else
{t.CITY} & ", " & {t.STATE} & " " &{t.POSTAL_CODE}

IT WORKS!!!!

 

[synapsevampire]

Ahhhhh, yeah, but if it's Intl then you don't understand the addresses, they tend to have different naming as they have different components, such as not having States.

Glad that you worked it out, and I suggest that you post specifics in the future, such as it being Intl, and or course you'll need another field, which is the Country.

-k

 

[kskid]

I use the following formula in creating my addresses. I find it an easier way to handle the null fields I may encounter.

//sample Address is 455 N 32ND E #123 Anytown, KS 67201
StringVar addr := "";

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

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

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

if IsNull({ADDRESSES.STREET_TYPE})then
addr := addr
else addr := addr + " " + {ADDRESSES.STREET_TYPE};

if IsNull({ADDRESSES.DIRECT_SUFFIX}) then
addr := addr
else addr := addr + " " + {ADDRESSES.DIRECT_SUFFIX};

if IsNull({ADDRESSES.SUB_NUMBER}) then
addr := addr
else addr := addr + " #" + {ADDRESSES.SUB_NUMBER};

if IsNull({ADDRESSES.CITY}) then
addr := addr
else addr := addr + " " + {ADDRESSES.CITY};

if IsNull({ADDRESSES.STATE}) then
addr := addr
else addr := addr + ", " + {ADDRESSES.STATE};

if IsNull({ADDRESSES.ZIP}) then
addr := addr
else addr := addr + " " + {ADDRESSES.ZIP};

if trim(addr) = "" then
"No address found"
else
trim(ADDR)

-lw

 

[CrystaLv]

synapsevampire,
they do not store Intl addresses differently. It goes 'let's put their Country name where our Zip Code was...' type of thing. So i am not fully aware yet what is going to happen but testing will show. Thanks


kskid,
I am studying your formula, than you.