ZIP code query not working (Access 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote