1

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#

1
  • Thanks, I have been trying to figure that out for days and came up with the solution a few minutes ago! I'll leave the answer below. Feb 27 at 11:35
0

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

1
  • 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.
    – J.D.
    Feb 27 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.