thread703-757058 I use the followi

[JohnXr]

thread703-757058
I use the following concatenated City, State Zip =[City] & ", " & [State] & " " & [Zip]. This works fine except when the City State and Zip fields are Null.
I then get a comma printed when I don't want it. How can I avoid this?

 

[sxschech]

If all fields will be null, you could try plus + instead of ampersand &. Otherwise you may need to use some logic. Will you have cases where you would have one or more of the fields with data or will it usually be all 3 or none of the three?

 

[JohnXr]

I tried plus in this way and it worked. =([City]&(", "+ [State])& " "&[Zip])
Thanks for your help.

 

[dhookom]

If sxschech's suggestion resolved your issue, please use the Great Post! link to provide some recognition and close the thread as resolved.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Axworthy]

A little long, but you could write it like this: [City] & iif(len(nz(City, "")) > 0 and len(nz(State, "")) > 0, ", ", "") & [State] & " " & [Zip]

This will only show the comma if there is both a city and a state.