Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    Dallas, Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ZIP code query not working (Access 2003)

    I have a problem with a query I am trying to run in Access. I am trying to link two different tables by their ZIP codes. Table1 has 5-digit ZIP codes (in other words, they display like this - 05938), and Table2 has numerically formatted ZIP codes (in other words, 05938 displays as 5938). Both are listed as Text Data Type.

    When I try to query using these two fields, anything in Table2 with a ZIP code that is not 5 digits is not querying. In the final query, the results only show ZIP codes over 10000 because 5938 does not query with 05938.

    Does anyone know of a way to expand the 00001-09999 ZIP's in Table2 so that I can query them against Table1? I was thinking maybe if there was an expression that I could do to convert the ZIP's to five-digits, I could insert that. Then I could create a MakeTable query that would push the information to a new table, and I could query Table 1 off of the new table. Does that make sense?

    Thank you so much in advance for your help.

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

    Re: ZIP code query not working (Access 2003)

    One possibility would be to create and run an update query that changes all zip code in Table2 whose length is 4 to "0" & [ZipField] where ZipField is the name of the field. You'd have to run this query only once.

    Another possibility is to create a select query based on Table2 with a calculated zip code column:

    Zip5: Right("0" & [ZipField], 5)

    You can create another query based on this one and on Table1. You should be able to join them on the calculated column Zip5 vs the zip field from Table1.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ZIP code query not working (Access 2003)

    One more possibility.
    Use the format function in the union or update query :
    Format(Zip,"00000")
    Francois

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Location
    Dallas, Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ZIP code query not working (Access 2003)

    Thank you so much, this did the trick! I apologize for waiting so long to post a response, I've been swamped at work. It really truly sucks when I actually have to work at 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
  •