Purpose:
Create a date range among the three most recent years from the beginning of each year to the current month/day for each year to be used in a query.
Ex:
1/1/2014-10/16/2014
1/1/2015-10/16/2015
1/1/2016-10/16/2016
Code:
("Between #" & CDate("1/1/" & Year(Date())) & "# And #" & Date() & "#") Or ("Between #" & CDate("1/1/" & Year(DateAdd("yyyy",-1,Date()))) & "# And #" & DateAdd("yyyy",-1,Date()) & "#") Or ("Between #" & CDate("1/1/" & Year(DateAdd("yyyy",-2,Date()))) & "# And #" & DateAdd("yyyy",-2,Date()) & "#")
Issue:
String doesn't convert to meaningful date range. Even a simpler version doesn't yield any results.
Code:
"(Between #" & CDate("1/1/" & Year(Date())) & "# And #" & Date() & "#)"
The second line of code does translate into a date range using VBA Immediate window.
Code:
(Between #1/1/2016# And #10/16/2016#)
However, when inserted into a query criteria the following is returned:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I doubt the code is too complicated so therefor it must be typed incorrectly. I tried to find a similar posting but could not.
Any suggestions?
Thank you.