I want to enter the fiscal year (18/19) and have the query only select from dates within that range. The year is March 1 – Feb 28/29. Is there a way to enter the string “18/19” or “21/22” and have those dates separated apart and turned into the parameter/criteria? I am not proficient at SQL, but this is roughly what I have in mind.
WHERE ([Combined All].Date_Read)>= #3/1/Left([Water Year:],2)# And ([Combined All].Date_Read)<=#2/1/Right([Water Year:],2)#;
Alternatively, I though of creating a string with the date and entering that into the WHERE, but I’m lost on how to do that
strYearOne = "03/01/" & left([Water Year:],2) strYearTwo = "03/01/" & right([Water Year:],2)
I’m not very familiar with SWL/Access syntax and I’m sure I’m mixing everything up. TIA
Advertisement
Answer
Consider DateSerial to build date in year, month, and day parts:
WHERE ([Combined All].Date_Read) >= DateSerial([StartYear], 3, 1) AND ([Combined All].Date_Read) < DateSerial([EndYear], 3, 1);
When above is run in MS Access (i.e., .exe application), the user will be prompted for these two parameters. However, query will fail if running MS Access as a backend (i.e., ODBC connection) where you will need to supply these parameters such as binding to qmark placeholders as used in Python ODBC, PHP ODBC, and VBA ADO:
WHERE ([Combined All].Date_Read) >= DateSerial(?, 3, 1) AND ([Combined All].Date_Read) < DateSerial(?, 3, 1);