How write criteria in expression to search a range and return valu

[Guest]

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
5455 40000 46000 TN
3725 90000 95000 CA
618 22000 25000 NY

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

45455 TN
93725 CA
22618 NY

 

[Guest]

Try this ---
SELECT [Table 1].[Zip Code], Table11.State
FROM Table11, [Table 1]
WHERE ((([Table 1].[Zip Code]) Between [Zip I] And [Zip II]));

 

[Steve]

The zipcodes in table 1 are missing the first digit. I presume that is a
typo. Create a query based on table 1. Put Zip Code in the first field. Put
the following expression in the second field:

ZipAndState:[Zip Code] & " " & DLookup("[State]","Table II","[Zip Code]
Between [Zip I] And [Zip II]")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)