With the following SQL statements, you can query previous time periods within your database (given the fact that you have a date field(s) in the table(s) that you are querying.


Current Week to DateSELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN

DateAdd("d", -((Weekday(Date()) - 1)), Date()) AND

Date();

Current Month to DateSELECT Table.DateField
FROM Table
WHERE (((Table.DateField) BETWEEN

DatePart("m",Date()) & "/1/" & DatePart("yyyy",Date()) AND

Date()));


Current Year to DateSELECT Table.DateField
FROM Table
WHERE (((Table.DateField) BETWEEN

"1/1/" & DatePart("yyyy",Date()) AND

Date()));

Previous MonthsPARAMETERS [Number of Previous Months to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN

DateAdd("m", -[Number of Previous Months to Query],
DatePart("m", Date()) & "/1/" & DatePart("yyyy", Date())) AND

DateAdd("d", -1,
DatePart("m", Date()) & "/1/" & DatePart("yyyy", Date()));

Previous Weeks (using "DAYS" parameter)PARAMETERS [Number of Previous Weeks to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN

DateAdd("d", (-[Number of Previous Weeks to Query] * 7),
DateAdd("d", -((Weekday(Date()) - 1)), Date())) AND

DateAdd("d", (([Number of Previous Weeks to Query] * 7) - 1),
DateAdd("d", (-[Number of Previous Weeks to Query] * 7),
DateAdd("d", -((Weekday(Date()) - 1)), Date())));

Previous Weeks (using "WEEKS" parameter)PARAMETERS [Number of Previous Weeks to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN

DateAdd("ww", -[Number of Previous Weeks to Query],
DateAdd("d", -((Weekday(Date()) - 1)), Date())) AND

DateAdd("ww", [Number of Previous Weeks to Query],
DateAdd("ww", -[Number of Previous Weeks to Query],
DateAdd("d", -(Weekday(Date())), Date())));

Previous YearsPARAMETERS [Number of Previous Years to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN

DateAdd("yyyy", -[Number of Previous Years to Query],
"1/1/" & DatePart("yyyy", Date())) AND

"12/31/" & DatePart("yyyy", DateAdd("yyyy", -1, Date()));

Adams fine work assumes the US date format, fair enough as Adam works in the States, and whilst most people will be able to adapt the code to other formats some may not, so I have taken the liberty of giving my versions. Being a DateSerial fan I have used that rather than Datepart, it has the added advantage of being format independent, returning the system default.

A couple of examples were not changed as they did not need to be.

Brian


Current Month to Date

SELECT Table.DateField
FROM Table
WHERE (((Table.DateField) BETWEEN
Dateserial(Year(Date()),Month(Date()),1) AND Date();



Current Year to Date

SELECT Table.DateField
FROM Table
WHERE (((Table.DateField) Between Dateserial(Year(Date()),1,1) And Date();



Previous Months

PARAMETERS [Number of Previous Months to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN
DateSerial(Year(Date()),Month(Date())-[Number of Previous Months to Query],1)
And DateSerial(Year(Date()),Month(Date()),0);

This pulls to the end of the previous month
So If we are in May and the number of previous months is 3 then we pull Feb to April.
Note that it does work over years you can quote 18 months for example



Previous Years

PARAMETERS [Number of Previous Years to Query] Short;
SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN
DateSerial(Year(Date())-[Number of Previous Years to Query],1,1)
AND DateSerial(Year(Date())-1,12,31);