setup Rolling Year criteria

[shimul]

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.

[nico5038]

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)

[shimul]

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.

[nico5038]

You can use the following function to test the needed formula:

Function x()

Dim dtDate As Date

dtDate = Date

Debug.Print "Startdate: " & dtDate - Weekday(dtDate) + 1
Debug.Print "End  date: " & DateSerial(Year(dtDate) - 1, Month(dtDate), Day(dtDate)) - Weekday(DateSerial(Year(dtDate) - 1, Month(dtDate), Day(dtDate))) + 2


End Function

By changing date into one of your values from the combo you can construct the BETWEEN.

Nic;o)

[shimul]

Thank you Nico.

I will check it and let you know.

[shimul]

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())

[nico5038]

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)

[shimul]

Thanks for reply.

It doesn't work.

Can you please check it?

Thank you.