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

setup Rolling Year criteria

P: 28
Need to get a formula which will give me rolling year to retrieve data.

For Example: when i run query this week, need date
>=2008/01/07 and <=2009/01/04 to cover the rolling year (week start "Monday" and week finished "Sunday") (previous week >=2007/12/31 and <=2008/12/28)

I have table colume, which call "I_Date" (all date store in this column) and get "week number" and "year" from "form" (pls see attachment).

Is there any way I can do it?

Thank you in advance.
Attached Images
File Type: jpg attachment.jpg (3.4 KB, 175 views)
Jan 5 '09 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
Week numbers are "relative" to the starting point chosen and can cause trouble. (1-1-2010 will return in some cases week 53 of the previous year...).
Personally I would take a selected date and correct it using the Weekday function that returns 1 to 7 depending on the day of the week.
This corrected date can be used to subtract a year and (after correcting this by + 1) to have the start and end date of the required BETWEEN in your query.

Nic;o)
Jan 5 '09 #2

P: 28
Thank you for your reply.

I have attached another image, where I changed little in my form (no more year in my form) and added start date (Monday) and end date (sunday) with each week number.

can you please help me to make the formula for rolling year?

Thank you.
Attached Files
File Type: zip attachment.zip (2.8 KB, 66 views)
Jan 6 '09 #3

nico5038
Expert 2.5K+
P: 3,072
You can use the following function to test the needed formula:

Expand|Select|Wrap|Line Numbers
  1. Function x()
  2.  
  3. Dim dtDate As Date
  4.  
  5. dtDate = Date
  6.  
  7. Debug.Print "Startdate: " & dtDate - Weekday(dtDate) + 1
  8. Debug.Print "End  date: " & DateSerial(Year(dtDate) - 1, Month(dtDate), Day(dtDate)) - Weekday(DateSerial(Year(dtDate) - 1, Month(dtDate), Day(dtDate))) + 2
  9.  
  10.  
  11. End Function
  12.  
By changing date into one of your values from the combo you can construct the BETWEEN.

Nic;o)
Jan 6 '09 #4

P: 28
Thank you Nico.

I will check it and let you know.
Jan 7 '09 #5

P: 28
Using query where i am getting result

Week Number -----------------Start_Date ------------------End_Date
2 ------------------------------2008/01/07 ------------------2008/01/13 11:59:59 PM
.
.
52 ---------------------------2008/12/22 -------------------2008/12/28 11:59:59 PM

But I want something like

Week Number ----------------Start_Date --------------------End_Date
2 -----------------------------------2008/01/07 -------------------2008/01/13 11:59:59 PM
.
.
1 ------------------------------------2008/12/29 -------------------2009/01/04 11:59:59 PM

I attached my query. Can you please check my query and tell me why i am not getting my desire result?

Here is my formula, I am using in query:

Start_Date (getting from my table)
>=DateAdd("yyyy",-1,DateAdd("d",-Weekday(Date()),Date()))
End_Date (getting from my table)
<=DateAdd("d",-Weekday(Date()),Date())
Attached Files
File Type: zip attchment.zip (4.9 KB, 59 views)
Jan 9 '09 #6

nico5038
Expert 2.5K+
P: 3,072
The end_date has an additional time (2009/01/04 11:59:59 PM) change the format of the field in the table to hold only a (short) date to make the query work.

Then use the algorithm from my comment instead of the DateAdd's...

Nic;o)
Jan 9 '09 #7

P: 28
Thanks for reply.

It doesn't work.

Can you please check it?

Thank you.
Jan 9 '09 #8

Post your reply

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