SQL SELECT QUERY with WHERE AND doesn't work-CodePudding

This shows records

sqlStr = "SELECT * FROM Table1 WHERE DateOfHoliday >= #" & d1 & "#"

this doesn't

sqlStr = "SELECT * FROM Table1 WHERE DateOfHoliday >= #" & d1 & "# AND DateOfHoliday <= #" & d2 & "#"

d1 and d2 are datetimes, the format of DateOfHoliday and d1 and d2 is the same (MM/DD/YYYY)

for example

Dim d1 As Date
d1 = #9/15/2021#
Dim d2 As Date
d2 = #11/29/2021#

One of the DateOfHoliday is 9/28/2021 and the Debug.Print strSQL returns :

SELECT * FROM Table1 WHERE DateOfHoliday >= #9/15/2021# AND DateOfHoliday <= #11/29/2021#

CodePudding user response：

Try this query with each condition in ( )

"SELECT * FROM Table1 WHERE (DateOfHoliday >= #" & d1 & "#) AND (DateOfHoliday <= #" & d2 & "#)"

CodePudding user response：

First, Date values carry no format. Next, most likely, your date field is Text. Try:

sqlStr = "SELECT * FROM Table1 WHERE DateValue(DateOfHoliday) >= #" & d1 & "# AND DateValue(DateOfHoliday) <= #" & d2 & "#"

CodePudding user response：

I agree with the previous answer that this is most likely a datatype problem. Alternatively to the code suggested in the previous answer you may try:

sqlStr = "SELECT * FROM Table1 WHERE CDate(DateOfHoliday) >= Cdate(#" & d1 & "#) AND CDate(DateOfHoliday) <= CDate(#" & d2 & "#)"

If this still fails, I suggest you try the following workaround:

sqlStr = "SELECT * FROM (SELECT * FROM Table1 WHERE DateOfHoliday >= #" & d1 & "#) WHERE DateOfHoliday <= #" & d2 & "#)"

More information in LightningGuide.net.