Query for partial zip code match

[dluvly001]

I have two tables. Table1 contains a field for Case Numbers, and a field for their associated Zip Codes(some 5 digit, some 9 digit). Table2 contains a list of 5 digit zip codes and the State they correspond to.

I'm trying to build a query to match the Zip Code from Table1 with the state from Table2, but can't figure out how to get matches for the 9 digit zip codes from Table1. I only want the query to look at the first 5 digits of the zip code field for a match.

Ex: It will match 85001 with Arizona, but will not match 85001-0000.

 

[PHV]

SELECT *
FROM Table1
INNER JOIN Table2 ON Left(Table1.[Zip Code],5)=Table2.[Zip Code]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dluvly001]

This worked perfectly, thank you very much!