DLookup with dates

[John Moore]

Re Access 2000.
Can someone please suggest how I can use DLookup to lookup a date (MarketDate) and return a price (CashPrice) relevant to that date. The code below works for date from 13th to 31st but not dates below this. Date format required is dd/mm/yy. The else works.
The code is below
If [PriceType] = "Cash" Then
ZZZ = "[MarketDate]= #" & Forms![frmTable2]!PurchaseDate & "#"

Price = DLookup("[CashPrice]", "tblCashPrice", ZZZ)
Else
Price = DLookup("[price]", "table1", "[contractno]=" & Forms![frmTable2]!ContractNo)
End If

 

[John Moore]

DLookup dates

Thanks Pat for the quick reply but the line of code results in a compile error. Market date is a date data type in the table formated dd/mm/yy.

 

[Rich]

Why not just use a combo box?

 

[John Moore]

dLookup with dates

Pat
Thanks for your help. The explanation helped a lot. I formated the date from the form (in a table as a date field) as follows and now seems to cope with days less the 13.

ZZZ = "[MarketDate]= #" & Format(Forms![frmTable2]!PurchaseDate, "mm/dd/yy") & "#"
Surely other ameteurs have this problem!