SQL Select statement with date syntax error

[Sunnray]

I've found 3 ways to use a variable for a date in the where clause. None of them return records. I've set breakpoints and the date is correct. MyRec.EOF is always true.

Set MyRec = MyDB.OpenRecordset("Select heure " & _
"from archives " & _
"where nas = '" & txtSIN & "' " & _
"and jour = #" & dReportCurrentDate & "#" & " " & _
"and jour = " & format(dReportCurrentDate, "yyyy/mm/dd") & " " & _
"and jour = " & format(dReportCurrentDate, "mm/dd/yyyy") & " " & _
"and heure is not null;")

If I leave out the date the query retrieves data. What is the correct format when using a variable as a date in your where clause?

Any help appreciated.

 

[Sunnray]

I found a fix to this issue. Its a combination of 2 lines.
"and jour = #" & Format(dReportCurrentDate, "mm\/dd\/yyyy") & "#" & " " & _

Link to site that helped me:
http://www.everythingaccess.com/tutorials.asp?ID=International-Dates-in-Access

This thread is resolved.