Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Query Combine Fields (AccessXP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Query Combine Fields (AccessXP)

    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])

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query Combine Fields (AccessXP)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •