Error Message Using Between And Operator

[Guest]

I get this error message when I try to run the query below:

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

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]

Any help would be appreciated.

 

[John Spencer]

You can try the following

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 really think you will have to use the following

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]

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