DLookup Help

[mattc66 via AccessMonster.com]

Hi All,

I am doing a DLookup. I want to lookup the cost based on 3 parameters in the
table. Calcode, Product and WoodType and then return the result. I think the
problem is my syntax. Can anyone help me with the below DLookup string?

Thanks

varWDT = Me.cboCC_Mtl.Column(4)
varRMT = "CC"

'Lookup costs
varLAB = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=100 & [Product]
=varRMT &_ [WoodType]=varWDT")
varBNC = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=101 & [Product]
=varRMT &_ [WoodType]=varWDT")
varFLR = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=102 & [Product]
=varRMT &_ [WoodType]=varWDT")
varLUX = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=103 & [Product]
=varRMT &_ [WoodType]=varWDT")
varSKT = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=104 & [Product]
=varRMT &_ [WoodType]=varWDT")
varCDR = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=1 & [Product]
=varRMT &_ [WoodType]=varWDT")
varFIX = DLookup("[CostPer]", "tblQuoteParamsNew", "[CalcCode]=105 &
[Product]=varRMT &_ [WoodType]=varWDT")

 

[Chegu Tom]

Your Where clause seems very convused

so you mean
"[CalcCode]= 100 AND [Product]='" & varRMT & "' AND [WoodType] ='" & varWDT
& "'"

watch for single and double quotes. I assumed that Product and WoodType are
Text fields and CalcCode is a number
use AND to connect your conditions

This is true if
CalcCode=100
and Product = Whatever is in varRMT
and WoodType = whatever is in varWDT

 

[John W. Vinson]

Hi All,

I am doing a DLookup. I want to lookup the cost based on 3 parameters in the
table. Calcode, Product and WoodType and then return the result. I think the
problem is my syntax. Can anyone help me with the below DLookup string?

The problem is that you're using the & character - a string-concatenation
operator - instead of the AND operator - a query logical operator. In
addition, you're using the NAMES of the string variables rather than the
values in those variables.

What you want to generate is a string like

[CalcCode]=100 AND [Product] = 31 AND [WoodType]=318

assuming that the Product and WoodType fields are numeric (if they're not,
you'll need some additional quotemarks).

You can build up this string by using the & operator to "stitch together"
various string pieces, both literal constant strings and the values of VBA
variables. Something like this, splitting it onto several lines using the " _"
VBA continuation character just so it's more readable in the newsgroup:

DLookup("[CostPer]", "tblQuoteParamsNew", _
"[CalcCode]=100 AND [Product] = " & varRMT _
& " AND [WoodType] = " & varWDT)

This takes a literal string

"[CalcCode]=100 AND [Product] = "

and appends whatever value is in varRMT;
to that string it appends the literal

" AND [WoodType] = "

and then the value of varWDT.

Note the blank! Without it you'ld get something like

[Product] = 31AND [Woodtype]

which will cause an error.

The need to do multiple DLookUps is really suggesting some design problems.
You should consider instead having a table with values for CalcCode, Product,
WoodType and Cost; you can join this to your table by joining on all three
fields to determine the costs, rather than using code which will become a pain
to maintain.