Skip to content
Advertisement

Access: User inputs years to query dates in that fiscal year

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);

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement