Access Date Time [Resolved]

[dglienna]

I tried to help someone, and realized that I didn't have a good sample to use of doing a date/time query from VB against an Access table. The only way that I can return any data is by using the LIKE operator, and don't want to do that. The latest thing was declaring variants instead of date, and using formatdatetime to convert the date and time. Still, I get no records returned!

Dim cn As New ADODB.Connection 'connection
  Dim rs As New ADODB.Recordset 'recordset
  Dim strTime  'date of entry
  Dim strdate
  Dim strSQL As String
  'initialize variable
  strTime = FormatDateTime("10:04:00", vbLongTime)
  strdate = FormatDateTime("03/07/05", vbShortDate)
  'get date of entry from user
  
  'open connection /database located in same folder as project
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db4.mdb;Persist Security Info=False"
 
    strSQL = "select  * FROM AS_Createsched " & _
          " WHERE (((AS_Createsched.CSIN_dates) = # " & strdate & " #) " & _
          " and (AS_Createsched.CSIN_times =  # " & strTime & " #)) "
    
    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
    
  Debug.Print rs.Fields(4)
  
  If rs.State = adStateOpen Then rs.Close

[Ecniv]

" WHERE (((AS_Createsched.CSIN_dates) = # " & strdate & " #) " & _

You have spaces between the hash and the date - remove these and retry

Also try as a full string to make sure it works first then put variables in

[dglienna]

The space didn't make a difference. We solved it anyways.

http://www.vbforums.com/showthread.php?t=327693