By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Access SQL date Format

P: 4
Expand|Select|Wrap|Line Numbers
  1. Sub test()
  2.  
  3. Dim dbsCurrent As Database
  4. Dim qdfBestSellers As QueryDef
  5. Dim qdfBonusEarners As QueryDef
  6. Dim rstTopSeller As Recordset
  7. Dim rstBonusRecipients As Recordset
  8. Dim strAuthorList
  9.  
  10. Set dbsCurrent = CurrentDb
  11. Set frequencetbl = CurrentDb.OpenRecordset("Date", dbOpenDynaset)
  12.  
  13.  
  14. Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
  15. With qdfBestSellers
  16.  
  17. .Connect = "ODBC;DATABASE=W;DSN=OSS"
  18.  
  19. .SQL = "SELECT STARTTIME FROM TABLE1 " & _
  20. "Where DateValue(STARTTIME)=#1/17/2007#"
  21.  
  22. Set rstTopSeller = .OpenRecordset()
  23.  
  24. rstTopSeller.MoveFirst
  25. End With
  26.  
  27.  
  28. With rstTopSeller
  29. Do While Not .EOF
  30.  
  31. frequencetbl.AddNew
  32. frequencetbl.Update
  33. .MoveNext
  34. Loop
  35. End With
  36.  
  37.  
  38. rstTopSeller.Close
  39. dbsCurrent.Close
  40.  
  41. 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?
Jan 18 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 18 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
  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 :
Expand|Select|Wrap|Line Numbers
  1. .SQL = "SELECT STARTTIME FROM TABLE1 " & _
  2.      "Where STARTTIME=#1/17/2007#"
Jan 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.