Access SQL date Format

[jonathanlim]

Sub test()
 
Dim dbsCurrent As Database
Dim qdfBestSellers As QueryDef
Dim qdfBonusEarners As QueryDef
Dim rstTopSeller As Recordset
Dim rstBonusRecipients As Recordset
Dim strAuthorList
 
Set dbsCurrent = CurrentDb
Set frequencetbl = CurrentDb.OpenRecordset("Date", dbOpenDynaset)
 
 
Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
With qdfBestSellers
 
.Connect = "ODBC;DATABASE=W;DSN=OSS"
 
.SQL = "SELECT STARTTIME FROM TABLE1 " & _
"Where DateValue(STARTTIME)=#1/17/2007#"
 
Set rstTopSeller = .OpenRecordset()
 
rstTopSeller.MoveFirst
End With
 
 
With rstTopSeller
Do While Not .EOF
 
frequencetbl.AddNew
frequencetbl.Update
.MoveNext
Loop
End With
 
 
rstTopSeller.Close
dbsCurrent.Close
 
End Sub

I am getting ODBC - Call Fail error. I guess the problem is with the "Where DateValue(STARTTIME)=#1/17/2007#". Any idea how to repair this?

[nico5038]

Hmm, did you check or the query works on a linked table ?

I also see a table named "Date", but that's also a reserved word.....

Nic;o)

[NeoPa]

1. If STARTTIME is a Date/Time field then DateValue would be unnecessary and incorrect.
2. I'm not sure about "Date" as a recordset name (as Nico says)

Try :

.SQL = "SELECT STARTTIME FROM TABLE1 " & _
     "Where STARTTIME=#1/17/2007#"