Building a query to pick between set months/days but different

[mtremblay1971]

Hi all

I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.

The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).

As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).

I do not want to change the query annually, just let it change the year automatically.

Hope this helps and I appreciate the assistance

 

[CJ_London]

What you require can be done but you need to clarify this

just let it change the year automatically.

What date or event will automatically change the date? - for example you could base the requirement on changing based on the current date i.e. if the current date is 08/31/2014 show all records from 09/01/2013 to 08/31/2014 and if the current date is 09/01/2014 then show all records from 09/01/2014 to 08/31/2015.

Or perhaps there is a system date of some sort you would refer to. Either way the criteria will be along the following lines.

WHERE RecordDate Between DateSerial(Year(myDate)+month(myDate)<9,9,1) and DateSerial(Year(myDate-month(myDate)>8,8,31)