MS Access / forcing a date range 2 months back, bound to this week

0
votes

I have a query where I need, as date criteria, the week ending two months prior.

So for example if I ran the query on Monday (as of right now, the last Monday was 2/1/2016), it would look at 11/29/2015 through 12/5/2015 inclusive (Sunday through Saturday).

And then next week if I ran it, it would focus on 12/6/2015 through 12/12/2015 (Sunday through Saturday).

However I need it to return this exact same date range no matter which weekday of the week I run it. So for example the date range 11/29/2015 through 12/5/2015 would be selected if I ran it on 2/1/2016 through 2/5/2016 (Mon-Fri).

I'm not sure what the best way is to go about this. I've considered somehow trying to find the next Saturday and then clocking that back a few weeks, but there doesn't seem to be a week option in dateadd().

1
datems-accessvba
Months and weeks do not correlate, so there is no straight solution to this. You will have to decide whether to look back either a count of months or a count of weeks. - Gustav
@Gustav I guess weeks would be more appropriate - AJJ

1 Answers

0
votes

To get the first weekday of a week from Sunday to Saturday:

FirstWeekDate = DateAdd("d", 1 - Weekday(Date()), Date())

To go back, say 8 weeks:

EightWeeksBack = DateAdd("ww", -8, FirstWeekDate)

Then you can just add/subtrack days to get your intervals, for example.

EightWeeksBackLast = DateAdd("d", 6, DateAdd("ww", -8, FirstWeekDate))