Formula column question

[mveino]

I want to make a field be a formula, but I'm having trouble... basically I want to concatenate a bunch of fields to make one string so I don't have to do it programatically.

I have an address table that separates streetname, house, apt, lot, etc... but not every house has an apt or lot, for example. So I want an "if" statement that if anything is in apt, to concat 'Apt ' + aptNum to the end of the string... but I can't figure out how to do an if statement and I can't find any good documentation.

Thanks!

 

[PHV]

Have a look at CASE WHEN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mveino]

that did it... however, one more question... how do i specifiy a null case? for example:

case apt when '' then '' when null then '' else 'Apt ' + rtrim(apt) END

this doesn't work for some reason... the following formula returns a <null> value for the field, when the apt field is null:

rtrim(house) + ' ' + rtrim(streetName) + case apt WHEN '' THEN '' WHEN null then '' ELSE 'Apt ' + rtrim(apt) END

 

[PHV]

And this ?
rtrim(house) + ' ' + rtrim(streetName) + case apt WHEN > '' THEN 'Apt ' + rtrim(apt) ELSE '' END

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mveino]

Hmm, that gives me an "Error validating the formula for column streetString" ...

 

[PHV]

Perhaps this ?
case apt when '' then '' when IS null then '' else 'Apt ' + rtrim(apt) END

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mveino]

same, error validating the formula... it doesn't seem to like the "IS" , either...

as soon as i change the apt field to just be nothing, like a space, a value is returned for my streetString field... but when it's null, the streetString field is <null>

very peculiar...

 

[mveino]

Figured it out... switched up the order of the case and it now works correctly using the > '' ... thanks!

rtrim(house) + ' ' + rtrim(streetName) + case WHEN apt > '' THEN 'Apt ' + apt ELSE '' END