Query with Zip Code Mis-match (5 digit vs 9 digit)

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi,

I have a query in which I am trying to match up zip codes stored in two
different tables. One table, sometimes, has the zip code as a 9 digit number
(xxxxx-xxxx), while the other table ALWAYS has zip codes as a 5 digit number.

I need ALL THE RECORDS from the first table (the one that has 5 and 9 digits
mixed together... and just need to match up a field in the second table... if
the zip code matches.

I tried to use the "LEFT()" function in the criteria field of the query...
but that didn't work because the "matching" occurs before that function is
executed...

Any suggestions? As a little background, table 1 is a table listing all the
sales invoices, with shipping data. Table 2 is a sales territory listing,
with each zip code assigned to a salesman. I need to merge the two... in
order to find out which invoice belongs to which salesman's territory.

Any help would be GREATLY appreciated...

Thanks
 
Hi,

I have a query in which I am trying to match up zip codes stored in two
different tables. One table, sometimes, has the zip code as a 9 digit number
(xxxxx-xxxx), while the other table ALWAYS has zip codes as a 5 digit number.
Click to expand...

Number fields can't contain internal hyphens - is this introduced with a
Format or is it a literal part of the field? Are these Text fields (which they
should be) or Number fields?
I need ALL THE RECORDS from the first table (the one that has 5 and 9 digits
mixed together... and just need to match up a field in the second table... if
the zip code matches.
Click to expand...

SELECT <whatever>
FROM firsttable INNER JOIN secondtable
ON firsttable.ZIP LIKE secondtable.ZIP & "*"

This wildcard will match 83660 to either 83660 or to 83660-6354, assuming that
the two fields are indeed text.
 
Number fields can't contain internal hyphens - is this introduced
with a Format or is it a literal part of the field? Are these Text
fields (which they should be) or Number fields?
Click to expand...

Because zip codes can begin with a leading 0 (as does mine, here in
Weehawken, NJ), zip codes should always be stored as text, never as
numbers.
 
Back
Top