VBA SQL Access DB Query Date Statement

I’m using VBA to connect to a Access Database. Having some problems when running date criteria for a query.

I have a cell (C7) with a date in it, and I want to query results based on that date, below is my code:

    'Query1 Populate
    query = "SELECT * FROM " & TABLE & " WHERE ReportRunDate = " & _
            Chr(35) & Format(Sheet1.Range("C7").Value, "yyyy-mm-dd") & Chr(35)
    MsgBox query
    Set Rs1 = Conn1.Execute(query)
    MsgBox Rs1.RecordCount

No Errors are given, but no results are given, if I take the output from the variable query and run it directly into access the results are there. What might be happening?

Answer

Try a different date format:

    'Query1 Populate
    query = "SELECT * FROM " & TABLE & " WHERE ReportRunDate = #" & _
            Format(Sheet1.Range("C7").Value, "mm/dd/yyyy") & "#"
    MsgBox query
    Set Rs1 = Conn1.Execute(query)
    MsgBox Rs1.EOF

Advertisement

Answer