Access SQL spanning years

[clintonf]

Hi all,

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

CREATE TABLE attendance
attendanceid INT AUTOINCREMENT,
last_name TEXT,
first_name TEXT,
classmonth INT,
classyear INT
class_date DATE

I want to be able to write a query that asks how many times John Doe attended class in the last 5 months.

I can design the query specifying the user-defined parameter [Month]:

...
classyear = Year(date())
AND classmonth BETWEEN ([Month] - 5) AND [Month]

There's no problem if I execute the query in September. But, if I execute the query in February there's a problem.

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!

[NeoPa]

Use a Date field (You don't tell us, but I assume [Class_Date] contains the relevant info) :

strSQL = "... " & _
         "WHERE ([Class_Date] >= #" & Format(DateAdd("m", -5, Date()),"m\/1\/yyyy") & "#)"

[clintonf]

Thanks NeoPa!

I've been trying out your suggestion and it's working great!

[NeoPa]

Very pleased to hear it :-)