Access Query Combine Fields (AccessXP)

Topic

New Reply

I have a query that includes the fields City, St and Zip. I want to combine them into one field.

If I put [city]+[st]+[zip], it works fine IF there is data in ALL 3 fields. But if data is missing in any of the fields nothing happens and it comes up blank.

Any hints so I get results even with 1 or 2 fields empty?

Thanks.

Reply | Quote

Replies

This is a peculiarity of + that can be very handy, but not in your situation. Use & to concatenate; you will probably want to insert commas and spaces too.

Very simple:
[city] & “, ” & [st] & ” ” & [zip]

Slightly more complicated, combining & and +:
[city] & (“, “+[st]) & (” “+[zip])

Reply | Quote

Thanks! Didn’t know about using &. And yes I was going to put in commas and spaces but they weren’t the problem – the lack of info was. Appreciate your prompt reply. Now I’ll get to work and put it all to work!

Reply | Quote

Thanks! Didn’t know about using &. And yes I was going to put in commas and spaces but they weren’t the problem – the lack of info was. Appreciate your prompt reply. Now I’ll get to work and put it all to work!

Reply | Quote

This is a peculiarity of + that can be very handy, but not in your situation. Use & to concatenate; you will probably want to insert commas and spaces too.

Very simple:
[city] & “, ” & [st] & ” ” & [zip]

Slightly more complicated, combining & and +:
[city] & (“, “+[st]) & (” “+[zip])

Reply | Quote