Matching Zip coded in a Query

[Ron Zack]

I have query that joins two tables using the Zip Codes (only show the records
where the Zip codes match). The problem is that in one table, the listing
of zip codes, it is 5 digit zip codes. In the main table some of the zip
codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I
want all the records as long as the 5 digit zip codes matches.

I quess I could delete the - and the last 4 digits on the main table, but it
would be nice to retain the whole zip codes. Is there a way to do this?

Thanks!!!!!

 

[Linq Adams via AccessMonster.com]

Use

Left(MainTableZipCodeField,5)

to compare to the original 5 digit Zip.

 

[Ron Zack]

This is probably the solution, but I am not sure how (where) to implement.
Do I do this in the SQL view?

 

[Lance]

Yes.

Look for the part that's something like .. ON <some field> = <some field>

Something to be aware of, once you make this change you will not be able to
edit the query with the query designer GUI. So get as much of it correct as
possible before making this change.

alternately, if you're not comfortable with working with the SQL directly,
you can create a seperate query which to parse your ZIP code values to 5
digit ( from a single table ), and then link that query to your other table
in a 2nd query. Not as elegant and not the "right way".. but will allow you
to work with the GUI the whole way through.