Changing Week definition in MS Access Query

Factual Questions
1

I have a report in Access and I need to be able to report on Week to Date and Previous Week. I have found some formulas that do this, but the defined week is Sunday through Saturday and I need it to be Monday through Sunday. Here is the formula I found for the previous week:

Year([Sales Date])*53 + DatePart(“ww”, [Sales Date]) = Year(Date())*53 + DatePart(“ww”, Date()) - 1

This works perfectly for pulling the data from the previous Sunday through the previous Saturday, but I need it to pull from the previous Sunday through the previous Monday. (I don’t even really follow exactly what the above formula is doing)

Any ideas on how to make this happen? My Google searching has been fruitless (I’m probably just not using the right search terms).

2

It’s been a while since I’ve done work in Access, but it lets you just add and subtract a number of days from date values, as I recall. So you should be able to subtract one day from all the date values in your expression:


Year([Sales Date]-1)*53 + DatePart("ww", [Sales Date]-1) = Year(Date()-1)*53 + DatePart("ww", Date()-1) - 1

Edit: Is this what you meant to say?

The change I suggested should make the range be the previous Monday through the previous Sunday.

3

Sorry, doing too many things at once. I need Monday through Sunday so I’ll give yours a try and let you know if it works.

Many thanks.

4

It worked perfectly… Thank you!

5

Great! Glad it worked for you.