strSQL between date range excel VBA

Question

I am trying to run an strSQL within Excel VBA between two date ranges.

See below part of my code relevant to this:

Dim DateMin As String
Dim DateMax As String

DateMin = Format$(Sheets("Setup").Range("c5").Value, "mm/dd/yyyy")        
DateMax = Format$(Sheets("Setup").Range("c6").Value, "mm/dd/yyyy")

strSQL = "SELECT [COSTCENTRE_CODE],[PROJECT_CODE],[HOME_VALUE],[CT_DEADLINE] FROM [AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW] where [CT_DEADLINE] between #" & DateMin & "# AND #" & DateMax & "#"

When I run this I get an "incorrect syntax nea '#'" error. The format of cells C5 + C6 are in the same mm/dd/yyyy format - am using the American date format as believe SQL only uses American dates? I have tried adjusting the dates but no luck.

When I run "debug.print strsql" I get the below:

SELECT [COSTCENTRE_CODE],[PROJECT_CODE],[HOME_VALUE],[CT_DEADLINE] FROM [AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW] where [CT_DEADLINE] between #02/01/2020# AND #03/31/2020#

I have tried removing the # around the dates and I no longer get the error, however no data shows at all - there is definitely data there for these dates.

Anyone have any ideas as believe this is an SQL issue rather than an Excel VBA issue?

I have now managed to fix this by using the following code: strSQL = "SELECT COSTCENTRE_CODE,PROJECT_CODE,HOME_VALUE,CT_DEADLINE FROM AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW where CT_DEADLINE between '" & DateMin & "' and '" & DateMax & "'" the date format needs to be m/d/yyyy for the cell range — PeterK
if your question is resolved, please don't write RESOLVED in its title. Insdead accept an answer as solution: Accepting Answers: How does it work?. • If none of the given answers solved your problem, but you found a solution yourself, you can even write an answer to your own question providing a solution and then accept it (you may need to wait 2 days before you can accept your own answer). This is how questions are marked as solved in Stack Overflow. — Pᴇʜ

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-04-21T07:08:48

Does running this SQL work? SELECT [COSTCENTRE_CODE],[PROJECT_CODE],[HOME_VALUE],[CT_DEADLINE] FROM [AA_CST_CENTRE_TRANSACTION_SIMPLE_VIEW] just to make sure the issue is in the date part and not before. If this is not running then the issue is in this part and not in the date part.

And I believe SQL is using YYYY-MM-DD hh:mm:ss.sss as date format. Check that out if you have done the first suggested test and this query runs.

And as far as I know only Access accepts # but in SQL-Server you would neet to use ' instead (which should also work for Access).

Also note that

BETWEEN '02/01/2020' AND '03/31/2020'

is actually:

BETWEEN '02/01/2020 00:00:00.000' AND '03/31/2020 00:00:00.000'

So notice that you are missing anything that happened after 12am on 03/31/2020.

strSQL between date range excel VBA

1 Answers