How can I span two years using SQL?
I'm using Access 2007 and writing the queries directly in the SQL editor.
Here's an example of what I'm trying to accomplish:
I have a table, 'attendance' that tracks class attendance
Expand|Select|Wrap|Line Numbers
- CREATE TABLE attendance
- attendanceid INT AUTOINCREMENT,
- last_name TEXT,
- first_name TEXT,
- classmonth INT,
- classyear INT
- class_date DATE
I can design the query specifying the user-defined parameter [Month]:
Expand|Select|Wrap|Line Numbers
- ...
- classyear = Year(date())
- AND classmonth BETWEEN ([Month] - 5) AND [Month]
So... how can I write the query so that it automatically spans two years if needed?
I'd like to avoid solutions requiring custom VBA, if possible so that I only have to go to one place to edit the query. I don't mind using Access' functions (like Year(date()).
Thanks!