DLookup with multiple conditions (1 Viewer)

[AnilBagga]

I have created a Dlookup condition in a Query as below which has issues

DLookUp("uncoatedrate","tblfabricconvrates", "tblpricingitemmaster.uncoatedgsm BETWEEN "tblfabricconvrates.GSMmin" AND "tblfabricconvrates.GSMMax"" AND "tblcustrfq.endcustomer = ' tblfabricconvrate.custID')

Never constructed something with so many conditions

The Query has tblpricingitemmaster where there is field "uncoatedGSM'

In this Query, there is relationship between tblcustrfq.erpfgcode and tblpricingitemmaster.erpfgcode

For the given erpfgcode in the tblcustrfq we need to look for a value Uncoatedrate in "tblfabricconvrates" such that the uncoated GSM of tblpricingitemmaster falls between GSMMin and GSMMax of tblpricingitemmaster

There is another condition which needs to be matched. The tblcustrfq.endcustomer = custid field in tblfabricconvrate

The DB is enclosed. Qry name is qryquotecaculation

 

[Gasman]

If you have variables you need to concatenate them using &
Surround text variables with ", dates with # and nothing for numerics.

Build the criteria bit by bit.
Use a string to hold the criteria and Debug.print that to ensure it is correct.

 

[Pat Hartman]

syntax:

DLookUp("uncoatedrate","tblfabricconvrates", "tblpricingitemmaster.uncoatedgsm BETWEEN tblfabricconvrates.GSMmin AND tblfabricconvrates.GSMMax AND tblcustrfq.endcustomer = tblfabricconvrate.custID')

All the criteria seems to be within the table itself. That isn't normal but OK. I'm guessing that you actually need some other criteria because otherwise, the DLookup() is likely to return random records out of the set that match the given criteria.

Actually, looking again, you are referencing two tables but the lookup only includes one so where is the other data coming from.

 

[AnilBagga]

syntax:

DLookUp("uncoatedrate","tblfabricconvrates", "tblpricingitemmaster.uncoatedgsm BETWEEN tblfabricconvrates.GSMmin AND tblfabricconvrates.GSMMax AND tblcustrfq.endcustomer = tblfabricconvrate.custID')

All the criteria seems to be within the table itself. That isn't normal but OK. I'm guessing that you actually need some other criteria because otherwise, the DLookup() is likely to return random records out of the set that match the given criteria.

Actually, looking again, you are referencing two tables but the lookup only includes one so where is the other data coming from.

Thanks.

You are right. I had referred to the wrong tables. Will correct