Date format in SQl to compare field with system date

[Louverril]

I have a problem I just can't get any sort of query findfirst or the sql below to correctly compare a value in a table with a supplied value.

I created a very simple situation to try and figure part of it out. I have one table with three fields ID, myname and Birthday. There are three records in there one has a birthday of 01/04/2008 (todays date - this is NOT an April Fool!) The Birthday field is formated as a date and just takes the system date format which is dd/mm/yyyy.

I am trying to get this code (below) to realise that a birthday with todays date does exist. However I keep getting the message "no birthdays match the system date". If I do a less than comparison it finds some dates.


Thanks for any help.

Lou

Dim db As Database
    Set db = CurrentDb
    Dim rec As DAO.Recordset
    Dim strSQL As String
 
    strSQL = "SELECT Top 1 * FROM table1 " _
    & "WHERE [Birthday] = #" & Date & "# " _
    & "ORDER BY [Birthday]"
 
    Set rec = db.OpenRecordset(strSQL)
 
    If rec.EOF Then
    MsgBox "no birthdays match the system date"
    Else
    MsgBox "birthdays match the system date"
    End If

[Louverril]

This is the solution

strSQL = "SELECT Top 1 * FROM table1 " _
       & "WHERE [Birthday] = Date()" _
       & "ORDER BY [Birthday]"

OR

Dim todaysdate As String
    todaysdate = Date
 
    strSQL = "SELECT Top 1 * FROM table1 " _
       & "WHERE [Birthday] = #" & Format(todaysdate, "mm\/dd\/yyyy") & "#"
       & "ORDER BY [Birthday]"

Lou

[NeoPa]

It seems like you've come across the SQL Date Literal issue. See Literal DateTimes and Their Delimiters (#) for a fuller explanation.