VBA SQL Access DB Query Date Statement

Question

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?

Access stores dates just as Excel: as doubles. Consider using double values to filter your data. Check my answer below — Barranka
– Barranka, Commented Aug 11, 2020 at 22:43

Tim Williams · Accepted Answer · 2020-08-11 22:33:28Z

2

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

answered Aug 11, 2020 at 22:33

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barranka Over a year ago

In Access (and Excel), dates and times are sensitive to Windows Regional Configuration. Your answer may work in this case, but it may not work with other regional configurations.

Tim Williams Over a year ago

I'm no Access expert - I based my answer in part off stackoverflow.com/questions/3724015/… and allenbrowne.com/ser-36.html which basically recommended using either "US" format or any format which is unambiguous. The second link notes that regional settings only apply when entering data. Not arguing against your approach...

Barranka Over a year ago

I brought the regional format issue to attention because it has caused me lots of tears and suffering in the past (that was one of the main reasons I stopped using Access).. The only (almost) bulletproof solution I found was relying on double values

Dharman · Accepted Answer · 2020-08-11 22:47:21Z

2

I'd convert the date value to a double value to avoid any formatting issues. Remember: MS Office programs (Excel & Access, for example) use doubles to store dates and times.

query = "SELECT * FROM " & TABLE & " WHERE ReportRunDate = " & _
        CDbl(Sheet1.Range("C2").Value)

I've used this strategy many times and it has worked for me.

edited Aug 11, 2020 at 22:47

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Aug 11, 2020 at 22:41

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Collectives™ on Stack Overflow

VBA SQL Access DB Query Date Statement

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related