dlookup

[melika]

Hi,

I am trying to use a dlookup to return the price of an item that meets two criteria. This is what I came up with, but it doesn't work. The part after the AND works by itself, but when I combine the two I either get a #Error or no values that meet the criteria.

Any suggestions? I can provide more info if needed.

Expr3: DLookUp("[Crt plan price]/1000","CPC List","[Plant] = ' " IIf(IsNumeric(Right([Date Code],2)),"Unk",Right([Date Code],2)) ' " And [Material] = " & [Item])

Thanks!

 

[Newbie100]

Try this.

Expr3: DLookUp("[Crt plan price]/1000", "CPC List", "[Plant] = '" & IIf(IsNumeric(Right([Date Code],2)), "Unk", Right([Date Code],2)) & "' And [Material] = " & [Item])

 

[ajetrumpet]

The part after the AND works by itself, but when I combine the two I either get a #Error or no values that meet the criteria.

Expr3: DLookUp("[Crt plan price]/1000","CPC List","[Plant] = ' " IIf(IsNumeric(Right([Date Code],2)),"Unk",Right([Date Code],2)) ' " And [Material] = " & [Item])

I'd like to read the explanation you have of the WHERE clause in that syntax...