Access SQL date format

[jonathanlim]

i have this script :

Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
With qdfBestSellers
.Connect = "ODBC;DATABASE=W;DSN=AAA"
.SQL = "SELECT START_TIME FROM table1 " & _
"Where START_TIME =#1/17/2007"

Set rstTopSeller = .OpenRecordset()
rstTopSeller.MoveFirst
End With

the problem is that the START_TIME in table1 is in 1/7/2007 1:00:00PM format. So what can i do to add in the script to only get the date and how do i modified the condition so that i can use date as my selection. Thanks.

 

[JerryKlmns]

If it was a table in access (works for linked also) you would just

PARAMETERS [Enter Date] DateTime;
SELECT .....
FROM ....
WHERE START_TIME >= [Enter Date] AND
START_TIME < [Enter Date] + 1;

But since my eye-sensor detects a recordset (DAO?) this might come from VBA in a module. And if you are reading from an SQL or Oracle RDBMS maybe you should switch to stored procedure and take a look here faq705-2531

 

[PHV]

Perhaps this ?
.SQL = "SELECT START_TIME FROM table1 " & _
"WHERE CLng(START_TIME)=#1/17/2007#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886