Date Query in Access DB

[msmith425]

I'm trying to do a SQL query against an Access 2002 DB with a Date/Time field. The format of the field is "mm.dd.yyyy hh:mm:ss AM"

I'm using a calendar control to get a date to search the database. My SQL statement looks like this:

Dim strSQL As String = "SELECT * FROM Table1 WHERE DateEntered Like " & Calendar1.SelectedDate

but it returns nothing.

or if I try and use "LIKE" as in the statement below I get nothing:

Dim strSQL As String = "SELECT * FROM Table1 WHERE DateEntered Like '*' & #4/30/2003# & '*'"

What am I missing? Any help greatly appreciated.
Marc

 

[majordog]

I think you are probably having problems with the formatting. I would use some of the date formatting methods and see if you can't format your values to be similar. For example:

Dim myDate as Date = Calendar1.SelectedDate

myDate = myDate.StartDate.ToLongDateString

Dim strSQL As String = "SELECT * FROM Table1 WHERE DateEntered = " & myDate

Just a guess...

 

[sunaj]

When querying datetimes through the JET engine, you have to use the US date format (regardless of what format you may have set access to display).
Examples:
"SELECT * FROM tblTest WHERE MyDateTime BETWEEN #04/30/1970 07:45:32# AND #03/24/1975 23:04:23#"
"SELECT * FROM tblTest WHERE MyDateTime=#05/14/1972 10:45:32#"

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[msmith425]

Problem Solved!

Thank you all for your input and nudging toward the answer. What I eventually did was as follows:

Dim myDate as Date = Calendar1.SelectedDate
Dim strSQL As String = "SELECT * FROM Table1 WHERE (DateEntered Between #" & myDate & " 0:1:0# And #" & myDate & " 23:59:0#)"

Thanks again.

Marc