By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Access SQL spanning years

P: 16
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
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE attendance
  2. attendanceid INT AUTOINCREMENT,
  3. last_name TEXT,
  4. first_name TEXT,
  5. classmonth INT,
  6. classyear INT
  7. 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]:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. classyear = Year(date())
  3. 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()).

Mar 16 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,419
Use a Date field (You don't tell us, but I assume [Class_Date] contains the relevant info) :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "... " & _
  2.          "WHERE ([Class_Date] >= #" & Format(DateAdd("m", -5, Date()),"m\/1\/yyyy") & "#)"
Mar 16 '12 #2

P: 16
Thanks NeoPa!

I've been trying out your suggestion and it's working great!
Mar 18 '12 #3

Expert Mod 15k+
P: 31,419
Very pleased to hear it :-)
Mar 18 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.