Suppress comma in calculated field given Null

0

0 1

I have this calculated field in a query:

CityState: ([City] & (", "+[State]))

Given various Null conditions, it will show:

City, State
City
, State

It's that last condition I need to address. If City is Null but State
is not, I need to suppress the comma and return just:

State

I can do this with a long and complicated IfNull statement, but I was
hoping to stick with the + option and keep it simple. Any suggestions?
 
M

Marshall Barton

I have this calculated field in a query:

CityState: ([City] & (", "+[State]))

Given various Null conditions, it will show:

City, State
City
, State

It's that last condition I need to address. If City is Null but State
is not, I need to suppress the comma and return just:

State

I can do this with a long and complicated IfNull statement, but I was
hoping to stick with the + option and keep it simple. Any suggestions?
Click to expand...


Try this:
CityState: Mid((", "+[City]) & (", "+[State]), 3)
 
Top