The Sql Result Is Inaccurate Because Of Wrong Sql Date Comparison Setting

I run SQL in excel using ADO in excel-VBA. And the results are displayed on an excel worksheet. There is a field called Date in worksheet 2014,2015,2016,2017 the data example :2/1/

Solution 1:

You need to create String variables for the fromDate and toDate dates and pass them to the WHERE clause. I suggest you use ISO date format. In addition to that to avoid problems with the column name date let's try enclosing the date column in the square brackets (do this also for the unioned tables), like this:

  fromDateStr = Format(fromDate, "yyyy-mm-dd")
    toDateStr = Format(toDate, "yyyy-mm-dd")

"WHERE [date] >= #" & fromDateStr & "# AND [date]<#" & toDateStr & "#"

On a side note, what happens with this expression DateSerial(toyear, ToMonth + 1, 1) when the toMonth is December? Shouldn't you use DateAdd function?

toDate = DateAdd("m", 1, DateSerial(toyear, ToMonth, 1))