How to set up a rolling year criteria in a query run via macro?

R

Rani

I've got a query which looks at rolling year to retrieve data and I'm not
able to set it to run via a macro because of the date criteria. Every week I
have to go into the design of the query and change the dates to include last
week. Is there a way around this?
 
J

John Spencer

What do you mean by a "rolling year"? Guessing that you want the data for the
previous year as defined by the last day of the previous week.

DateAdd("d",-WeekDay(Date()),Date()) will give you the last day of the
previous week

So the criteria is probably
BETWEEN DateAdd("yyyy",-1,DateAdd("d",-WeekDay(Date()),Date())) AND DateAdd("d",-WeekDay(Date()),Date())
I've got a query which looks at rolling year to retrieve data and I'm not
able to set it to run via a macro because of the date criteria. Every week I
have to go into the design of the query and change the dates to include last
week. Is there a way around this?
Click to expand...
-------
 
R

Rani

For eg:- when running the query this week, I have to change the dates to
=22/11/04 And <21/11/05 to cover the rolling year for week ending last
Click to expand...
Sunday (prev wk it was >=15/11/04 And <14/11/05)

So, how do I incoporate that into my query?
 
J

John Spencer

Try the formula I posted. It may need an adjustment by a day either way. If
you can't figure out how to adjust the dates that are generated by the formula,
post back.
 
R

Rani

Sorry, Could you tell me exactly how to write the formula in the query, if I
am to run it for this week?

Much appreciated and many thanks.

Rani.
 
J

John Spencer

Assuming you are using the query grid.

Put this
BETWEEN DateAdd("yyyy",-1,DateAdd("d",-WeekDay(Date()),Date())) AND DateAdd("d",-WeekDay(Date()),Date())
In the criteria "Cell" under your date field.

What that does is build two dates based on your current system date.
DateAdd("d",-WeekDay(Date()),Date())
returns Nov 19, 2005 which is Saturday of the prior week, If you want Sunday,

This formula
DateAdd("yyyy",-1,DateAdd("d",-WeekDay(Date()),Date()))
takes that and subtracts a year giving you Nov 19,2004

You can use your scheme
DateAdd("yyyy",-1,DateAdd("d",-WeekDay(Date()),Date())) And <
Click to expand...
DateAdd("d",-WeekDay(Date()),Date())

Adjusting to get the dates you posted of 22/11/04 and 21/11/05 would probably be
using this as the criteria.
DateAdd("yyyy",-1,DateAdd("d",3-WeekDay(Date()),Date())) And < DateAdd("d",2-WeekDay(Date()),Date())
Click to expand...
Sorry, Could you tell me exactly how to write the formula in the query, if I
am to run it for this week?

Much appreciated and many thanks.

Rani.
Click to expand...
 
R

Rani

Many Thanks ....

John Spencer said:
Assuming you are using the query grid.

Put this
BETWEEN DateAdd("yyyy",-1,DateAdd("d",-WeekDay(Date()),Date())) AND DateAdd("d",-WeekDay(Date()),Date())
In the criteria "Cell" under your date field.

What that does is build two dates based on your current system date.
DateAdd("d",-WeekDay(Date()),Date())
returns Nov 19, 2005 which is Saturday of the prior week, If you want Sunday,

This formula
DateAdd("yyyy",-1,DateAdd("d",-WeekDay(Date()),Date()))
takes that and subtracts a year giving you Nov 19,2004

You can use your scheme
DateAdd("d",-WeekDay(Date()),Date())

Adjusting to get the dates you posted of 22/11/04 and 21/11/05 would probably be
using this as the criteria.
Click to expand...
 
Top