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

 

[Pat Hartman]

Text formatted dates need to be in US format when used in queries. The alternative is to use the Format() function to format the table field --

ZZZ = "Format([MarketDate],"dd/mm/yy")= #" & Forms![frmTable2]!PurchaseDate & "#"


If you need to compare two text format dates using either the < or > operators, the dates MUST be formatted in yy/mm/dd order or they must be contained in fields of date data type.

 

[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?

 

[Pat Hartman]

The line of code I posted was just a replacement for one line that you posted. It needs to be used with the rest of the statement. It can't be used alone.

The format of a field is only relevant when it is being displayed. Dates are stored internally in a double precision field and formatted for display purposes according to your specific instructions or according to the default Windows date format. Part of your statement references a date in a text format (the form field) the other part references a date in a table which is always double precision. Therefore, Access needs to convert one date or the other to compare them. Because Access is US-centric, it assumes that text dates are in US format unless the date is non-ambiguous such as 13/11/92. Since the max for month is 12, the first field must be day.

 

[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!