 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005How to set up a rolling year criteria in a query run via macro?
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? ------- 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 Sunday (prev wk it was >=15/11/04 And <14/11/05) So, how do I incoporate that into my query? > 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. [quoted text clipped - 10 lines] > > week. Is there a way around this? > ------- 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. > 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 [quoted text clipped - 16 lines] > > > week. Is there a way around this? > > ------- 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. > 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, [quoted text clipped - 20 lines] > > > > week. Is there a way around this? > > > ------- 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 < 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()) > Sorry, Could you tell me exactly how to write the formula in the query, if I > am to run it for this week? [quoted text clipped - 27 lines] > > > > > week. Is there a way around this? > > > > ------- Many Thanks .... > Assuming you are using the query grid. > [quoted text clipped - 49 lines] > > > > > > week. Is there a way around this? > > > > > ------- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.