Date syntax error in DLookup

[AndersNO]

Hi.
I'm trying to use DLookup to check wheter a specific date value is present in a table or not.
The date to search for is stored in the variable datadate. The value of datadate i set by pulling the max value of a date field in a temporary table.
The table i want to search in is Holdings and the field I want to serch in is Date. Thus my DLookup syntax is

Dim datadate as Date

datadate = DMax("DATE", tablename & "_temp")

If IsNull(DLookup("[DATE]", "Holdings", "[DATE] = #" & datadate & "#")) Then

However when i try to run the sub i get an error message saying that there is a syntax error in the date in the expression . What I find strange is that VBA recognizes datadate as a date variable (IsDate(datadate)=true), for wich the value is 24.02.2011 (norwegian local date format). If i try passing the date by harcoding the date into the DLookup function using the format MM/DD/YYYY the value is accepted as a date.

Can anybody see whats wrong with my syntax or know of a way to work around the issue of different local formats?

Any help would be appreciated

 

[JANR]

If IsNull(DLookup("[DATE]", "Holdings", "[DATE] = " & Format(datadate, "\#mm\/dd\/yyyy\#")) Then

However DATE is a reserved word you should not use.

Hilsen
JR

 

[AndersNO]

Thanks for the reply. Altough I was not able to use the code excactly as you wrote it I got DLookup to accept the date by using:

If IsNull(DLookup("HOLDDATE", "Holdings", "HOLDDATE = #" & Format(datadate, "mm/dd/yyyy") & "#" Then

As you see I have also changed DATE to HOLDDATE as sugested by you.
The problem now howevere is that Dlookup can not find any records satisfying the condition even tough they evidently are present in the table.
The HOLDDATE column contains several dates on the format DD.MM.YYYY (standard norwegian date format). I have tried changing the date format in the table to MM/DD/YY but that had no affect at all.

Any sugestions?

 

[JANR]

How a date is displayed have nothing to do here since dates are stored as number, but if your "Holddate" field is set to text and NOT date/time then I would expect that you get an error on yorr Dmax statement.

just as a test this function found the date and I also use dd/mm/yyyy.

Function fobbar()
Dim datadate As Date
datadate = DMax("[holddate]", "holdings")

If DLookup("[holddate]", "Holdings", "[holdDATE] = " & Format(datadate, "\#mm\/dd\/yyyy\#")) Then
Debug.Print "found"
Else: Debug.Print "Lost"
End If
 
JR
End Function