How do I match a partial field in a query

[Guest]

I have a master table with a 9 digit zip code. I need to match it to a table
of 5 digit zip codes. How do I get it to match only the first 5 of the 9
digit field?

 

[John Spencer]

You can use a non-equi join.
SELECT Master.ZipCode, Match.ZipCode
FROM Master INNER JOIN Match
ON Master.ZipCode Like Match.ZipCode & "*"

Or

SELECT Master.ZipCode, Match.ZipCode
FROM Master INNER JOIN Match
ON Left(Master.ZipCode,5) = Match.ZipCode

 

[John Vinson]

I have a master table with a 9 digit zip code. I need to match it to a table
of 5 digit zip codes. How do I get it to match only the first 5 of the 9
digit field?

Two ways:

- Put a calculated field in the Query by typing

Zip5: Left([Zip], 5)

and use it for your search criteria. This has the disadvantage that
Access can't use any indexes on the zip field so for large tables it
may be slow.

- Use a criterion of

LIKE [Enter zip:] & "*"

This uses a wildcard so it will match either a five- or nine-digit
zip. It also has the advantage (or disadvantage, maybe) that the user
can type just a few digits of the zip - say 836 to get all the
zipcodes in that area.

John W. Vinson[MVP]