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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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...
-------
 
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?
 
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.
 
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.
 
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...
 
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...
 
Back
Top