DLookup Problem

[mugman17]

I am trying to create a DLookup with 2 criteria. This is what I have so far.

DLookUp("[PriceAmount]","tblPrice","Market=" & [Market] And "[PriceDate]=#" & #01/01/2004# & "#")

Its not working, so far it seems to pull out only the first record in the table. What am I doing wrong?

Thanks.

 

[Peter D]

Try this:

DLookUp("[PriceAmount]","tblPrice","Market=" & [Market] " And [PriceDate]=#" & #01/01/2004# & "#")

Hope this helps,

 

[WayneRyan]

mug,

Dim ThePrice As Currency
ThePrice = Nz(DLookUp("[PriceAmount]","tblPrice","[Market] = '" & Me.Market & "' And [PriceDate] = #01/01/2004#"), 0)

I'm assuming that Market is a control on your form and is a string.

Wayne

 

[Mile-O]

When it comes to strings, this is my personal preference.

Dim ThePrice As Currency
ThePrice = Nz(DLookUp("[PriceAmount]","tblPrice","[Market] = """ & Me.Market & """ And [PriceDate] = #01/01/2004#"), 0)

Just in case there is the chance that the Market field may include values using the apostrophe character.