Database Administration
query ms-access date
Updated Mon, 15 Aug 2022 12:08:22 GMT

Return only records with a date after the most recent September 1st


I have a table with data with a date column called "dtAssessmentDate" in a table called "tblAssessmentRecords". What I want to achieve is for a select query to return only the records from that table where the date value is after the last September 1st.

For example, if the current date is #27/02/2021# then the query would return the records with any date after #01/09/2020# as that was the most recent September 1st in the past.

I can do this for a record after a fixed date with:
[dtAssessmentDate] > #01/09/2020#
but I need the fixed date to roll over as the years go on, so if the current date was 27/02/2022, the query would return records after #01/09/2021#




Solution

Answered my own question, leaving it here for refence for others as I couldn't find the solution anywhere else. By adding 4 months to the date from "dtAssessmentDate" I was able to make the range of values I needed to return as any date from a single year. I extracted the year I am looking for by doing the same 4 month addition to the current date, then using DatePart to exctract the year and only return records who's year matches the extracted year.

Expression in criteria box in access is:

DatePart("yyyy",DateAdd("m",4,[dtAssessmentDate]))=DatePart("yyyy",DateAdd("m",4,Date()))





Comments (1)

  • +0 – Another thing you could do is compare the month integer, if it's < 9 then use 01/09/Current year - 1 else use 01/09/CurrentYear. Note that's pseudo-code, as I'm not familiar with what functions are available in MS Access. — Feb 27, 2021 at 13:00