SQL SELECT QUERY with WHERE AND doesn't work

Question

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#

Edit question to provide sample data and desired output as text tables. Are your dates in U.S. structure (MM/DD/YYYY)? — June7, Sep 15 '21 at 11:47
You could try to use `BETWEEN` instead. https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_between_date&ss=-1 — VirussInside, Sep 15 '21 at 11:48
give concrete examples of values for `d1` and `d2`. Make sure `d1` is the older date of those 2 values. — Pᴇʜ, Sep 15 '21 at 11:49
Use a `Debug.Print strSQL` on the next line to see what the complete SQL statement looks like. — Applecore, Sep 15 '21 at 11:49
If you want records where DateOfHoliday is between d1 and d2, try BETWEEN AND: `WHERE DateOfHoliday BETWEEN #" & d1 & "# AND #" & d2 & "#"`. However, what you tried should produce the same output. — June7, Sep 15 '21 at 11:50
Never use string concatenation with dates since how this is handled depends on computer locale settings. Use parameters instead: [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/a/49509616/7296893) — Erik A, Sep 15 '21 at 11:54
See [Examples of using dates as criteria in Access queries](https://support.microsoft.com/en-us/office/examples-of-using-dates-as-criteria-in-access-queries-aea83b3b-46eb-43dd-8689-5fc961f21762) at section *"Contain values within a date range (between two dates)"*. Your code should work as long as `DateOfHoliday` field has a date format (can you verify?). Can you ensure that you have dates in your database between those 2? — Pᴇʜ, Sep 15 '21 at 13:35

score 0 · Answer 1 · answered Sep 15 '21 at 12:12

0

Try this query with each condition in ( )

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

answered Sep 15 '21 at 12:12

VirussInside

187
17

2

Those parentheses will not change behavior. The criteria should work without them. Something else is issue. – June7 Sep 15 '21 at 13:17

score 0 · Answer 2 · answered Sep 15 '21 at 17:59

0

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 & "#"

answered Sep 15 '21 at 17:59

Gustav

53,498
7
29
55

score 0 · Answer 3 · answered Sep 16 '21 at 11:39

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.

SQL SELECT QUERY with WHERE AND doesn't work

3 Answers3