DLOOK UP function in Query Help

[penndro]

I am trying to look up values in a table from my query. the lookup has two criteria:

Zoned Rate: Dlookup("rate","ZonedRates","[Zone]=" & [Vendor Zone]AND [Per PcWgt] BETWEEN [Lwgt(lbs)] AND [Wgt(lbs])

The Lookup returns the first value in the lookup table for the field "rate".

I truly would apprecite it if someone can please help me to format this properly so it processes correctly. I have tried adding "" marks around fields and also added table name (i.e. [ZonedRates].[Lwgt(lbs)]) however those don't work. Here are the format of the two tables:

Table 1 (ZonedRates)
---------
Zone
LWgt(lbs) ------ Min Wgt
Wgt(lbs) ------ Max Wgt
Rate

Table 2 (VendorZones) -- this table is in my query
-----------
Vendor Zone
Per PcWgt


From the info Vendor Zone and Per PcWgt i am trying to find the correct rate that falls between the range of Min wgt (Lwgt(lbs) and Max Wgt (Wgt(lbs).

Any help would truly be appreciated. Spent almost a whole day trying to fix this or get online help but still not working.

Thanks in advance.

 

[PWise]

Zoned Rate: Dlookup("rate","ZonedRates","[Zone]=" & [Vendor Zone] & " AND " & [Per PcWgt]& " BETWEEN [Lwgt(lbs)] AND [Wgt(lbs])

 

[penndro]

Thanks Pwise.

I copied and pasted directly into my query what you posted above; however, I am now getting this error:

"The expression you entered has an invalid string.

A string can be up to 2048 characters long, including opening and closing quotation marks."

Please notice you have an open quotation mark after the last amperstand (before BETWEEN).

 

[dhookom]

It is critical to understand the data types and how they are delimited in these expressions. If all fields are numeric then try:

Zoned Rate: Dlookup("rate","ZonedRates","[Zone]=" & [Vendor Zone] & " AND " & [Per PcWgt] & " BETWEEN [Lwgt(lbs)] AND [Wgt(lbs]")

If Zone is text, try:

Zoned Rate: Dlookup("rate","ZonedRates","[Zone]=""" & [Vendor Zone] & """ AND " & [Per PcWgt] & " BETWEEN [Lwgt(lbs)] AND [Wgt(lbs]")

Duane
Hook'D on Access
MS Access MVP

 

[penndro]

Hi Duane,

Thanks so much for helping me with this.

I tried using:
Zoned Rate: Dlookup("rate","ZonedRates","[Zone]=" & [Vendor Zone] & " AND " & [Per PcWgt] & " BETWEEN [Lwgt(lbs)] AND [Wgt(lbs]")

All fields are NUMERIC in type. But now i get this error:
Syntax error (missing operator) in query expression '[Zone] = AND 1 BETWEEN [(Lwgt(lbs)] AND [Wgt(lbs)]'.

 

[dhookom]

The issue is that you have some records in your table that don't have a value in the [Vendor Zone]. Either filter these out or handle them.

Duane
Hook'D on Access
MS Access MVP