Compare date in table to system date using SQl statement

[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 mm/dd/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.

I am desperate! !!!! See also related question:
http://www.microsoft.com/office/com...cess&mid=99f07e19-4659-47c6-b6ba-3902e6045184

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]

SORRY - typo. The system date is in the format dd/mm/yyy hence todays date.

Sorry.

 

[Jeff Boyce]

Format is just a display, the comparison depends on the actual value(s)
stored.

Sometimes folks use a "date" field when it actually stores a date/time
value. Comparing today's date (Date()) with a date/time value (2:30 this
afternoon) will NOT match!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Louverril]

Jeff,

This is just a simple table - you add a field called Birthday and set it to
a date/time in the third column of the main grid - don't even look at the
lower details section.

Surely you should be able to compare Date to this? If not how can you
compare the current date to date field???

The match of Birthday to system date works fine if you go through query
design.


Thanks Lou

Format is just a display, the comparison depends on the actual value(s)
stored.

Sometimes folks use a "date" field when it actually stores a date/time
value. Comparing today's date (Date()) with a date/time value (2:30 this
afternoon) will NOT match!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 mm/dd/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.

I am desperate! !!!! See also related question:
http://www.microsoft.com/office/com...cess&mid=99f07e19-4659-47c6-b6ba-3902e6045184

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 (OK for you US types...)

Dim todaysdate As String
todaysdate = Date

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


Lou

Jeff,

This is just a simple table - you add a field called Birthday and set it to
a date/time in the third column of the main grid - don't even look at the
lower details section.

Surely you should be able to compare Date to this? If not how can you
compare the current date to date field???

The match of Birthday to system date works fine if you go through query
design.


Thanks Lou

Format is just a display, the comparison depends on the actual value(s)
stored.

Sometimes folks use a "date" field when it actually stores a date/time
value. Comparing today's date (Date()) with a date/time value (2:30 this
afternoon) will NOT match!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

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 mm/dd/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.

I am desperate! !!!! See also related question:
http://www.microsoft.com/office/com...cess&mid=99f07e19-4659-47c6-b6ba-3902e6045184

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