Query using dlookup

[wilbur13]

I have two tables, table I contain addresses complete with five (5) digit zip
codes. Table II contain three fields of data, zip code beginning range and
zip code ending range and a state code for the corresponding zip beginning
and zip ending range.

I’m tying to create a query in Access that will look up the zip
codes from table I and assign the corresponding state designation from table
II.

I have tried using d look but I've had no luck.

Also, my tables have no relationship to each other ( no link or primary
key), would this prevent me from creating a query?


Here is an example of the tables


Table I Table I I

Zip Code Zip I Zip II State
45455 40000 46000 TN
93725 90000 95000 CA
22618 22000 25000 NY


The query results that I would like should appear as the following:

45455 TN
93725 CA
22618 NY

 

[Ofer Cohen]

Try this dlookup

NewZip: Dlookup("[Zip Code]","[Table I]","[Zip Code] Between " & [Zip I] & "
And " & [Zip II])

If the Zip field it a text field, then you need to add a single quote

NewZip: Dlookup("[Zip Code]","[Table I]","[Zip Code] Between '" & [Zip I] &
"' And '" & [Zip II] & "'")