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

[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

 

[raskew via AccessMonster.com]

I tried to use the "LEFT()" function in the criteria field of the query

Rather than Left(MyZip, 5) in the criteria field, you need to create a
calculated field, e.g.
MyZip: Left(MyZip,5) and use that field to make your comparisons from.

Bob

 

[John W. Vinson]

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.

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.

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.

 

[David W. Fenton]

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?

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.