Dlookup Using Expression Builder

[Guest]

I do not know SQL but I am familar with Access. I need to build an expression
using dlookup for the situation below.

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?


Below are examples of the two tables that I have

Table I

Zip Code
45455
22618
93725

Table I I
Zip I Zip II State
40000 46000 TN
90000 95000 CA
22000 25000 NY

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

45455 TN
93725 CA
22618 NY

 

[Douglas J. Steele]

Really, using SQL is the best (if not only) approach.

Create a new query, add the 2 tables to it, but don't create a relationships
between them.

Drag the Zip Code field from Table 1 into the grid, and the State code from
Table 2.

Now, go into the SQL view of the query (through the Query menu).

You should see something like:

SELECT [Table 1].[Zip Code], [Table 2].State
FROM [Table 1], [Table 2]

Change that to:

SELECT [Table 1].[Zip Code], Nz([Table 2].State, "Not found")
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1].[Zip Code] BETWEEN [Table 2].[Zip I] AND [Table 2].[Zip II]

 

[John Spencer]

You should be able to use a non-equi join to do this

SELECT TableI.ZipCode, TableII.State
FROM TableI INNER JOIN TableII
ON TableI.ZipCode >= TableII.ZIPI
And TableI.ZipCode <=TableII.ZIPII

Non-equi joins cannot be constructed in the query grid, but must be
built in the SQL window. If you feel that you cannot do this then you
can use DLookup.

With DLookup

Field: TheState: DLookup("State","TableII","ZIPI<=""" & [ZipCode] """
AND ZIPII >=""" & [ZipCode] & """")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thanks for your help Doug.

I followed your instructions (see below),

SELECT Data.[Zip Code], Nz([5 DigitZip].State, "Not found")
FROM [Data] LEFT JOIN [5 DigitZip]
ON Data.[Zip Code] BETWEEN [5 DigitZip].[Zip I] And [5 DigitZip].[Zip II]

but I'm getting the following message below:

"Between operator without And in query expression 'Data.[Zip Code] BETWEEN
[5 DigitZip].[Zip I]'

Any suggestions?

Really, using SQL is the best (if not only) approach.

Create a new query, add the 2 tables to it, but don't create a relationships
between them.

Drag the Zip Code field from Table 1 into the grid, and the State code from
Table 2.

Now, go into the SQL view of the query (through the Query menu).

You should see something like:

SELECT [Table 1].[Zip Code], [Table 2].State
FROM [Table 1], [Table 2]

Change that to:

SELECT [Table 1].[Zip Code], Nz([Table 2].State, "Not found")
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1].[Zip Code] BETWEEN [Table 2].[Zip I] AND [Table 2].[Zip II]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I do not know SQL but I am familar with Access. I need to build an
expression
using dlookup for the situation below.

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?


Below are examples of the two tables that I have

Table I

Zip Code
45455
22618
93725

Table I I
Zip I Zip II State
40000 46000 TN
90000 95000 CA
22000 25000 NY

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

45455 TN
93725 CA
22618 NY

 

[Douglas J. Steele]

Either try John's suggestion:

SELECT Data.[Zip Code], Nz([5 DigitZip].State, "Not found")
FROM [Data] LEFT JOIN [5 DigitZip]
ON Data.[Zip Code] >= [5 DigitZip].[Zip I]
AND Data.[Zip Code] <= [5 DigitZip].[Zip II]

or see whether parentheses make a difference:

SELECT Data.[Zip Code], Nz([5 DigitZip].State, "Not found")
FROM [Data] LEFT JOIN [5 DigitZip]
ON (Data.[Zip Code] BETWEEN [5 DigitZip].[Zip I] And [5 DigitZip].[Zip II])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for your help Doug.

I followed your instructions (see below),

SELECT Data.[Zip Code], Nz([5 DigitZip].State, "Not found")
FROM [Data] LEFT JOIN [5 DigitZip]
ON Data.[Zip Code] BETWEEN [5 DigitZip].[Zip I] And [5 DigitZip].[Zip II]

but I'm getting the following message below:

"Between operator without And in query expression 'Data.[Zip Code] BETWEEN
[5 DigitZip].[Zip I]'

Any suggestions?

Really, using SQL is the best (if not only) approach.

Create a new query, add the 2 tables to it, but don't create a
relationships
between them.

Drag the Zip Code field from Table 1 into the grid, and the State code
from
Table 2.

Now, go into the SQL view of the query (through the Query menu).

You should see something like:

SELECT [Table 1].[Zip Code], [Table 2].State
FROM [Table 1], [Table 2]

Change that to:

SELECT [Table 1].[Zip Code], Nz([Table 2].State, "Not found")
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1].[Zip Code] BETWEEN [Table 2].[Zip I] AND [Table 2].[Zip II]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I do not know SQL but I am familar with Access. I need to build an
expression
using dlookup for the situation below.

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?


Below are examples of the two tables that I have

Table I

Zip Code
45455
22618
93725

Table I I
Zip I Zip II State
40000 46000 TN
90000 95000 CA
22000 25000 NY

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

45455 TN
93725 CA
22618 NY