Remember Me?

 Straight Dope Message Board Remember Me?

 Thread Tools Display Modes
#1
08-13-2019, 11:19 AM
 Guest Join Date: Jul 1999 Location: Central Indiana Posts: 3,927

## Changing Week definition in MS Access Query

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
08-13-2019, 11:42 AM
 Guest Join Date: Sep 2001 Location: Canada Posts: 2,098
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:

Code:
`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?
Quote:
 Originally Posted by Spud I need it to pull from the previous Sunday through the previous Monday.
The change I suggested should make the range be the previous Monday through the previous Sunday.

Last edited by borschevsky; 08-13-2019 at 11:45 AM.
#3
08-13-2019, 12:02 PM
 Guest Join Date: Jul 1999 Location: Central Indiana Posts: 3,927
Quote:
 Originally Posted by borschevsky 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: Code: `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.
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
08-14-2019, 10:03 AM
 Guest Join Date: Jul 1999 Location: Central Indiana Posts: 3,927
It worked perfectly... Thank you!
#5
08-14-2019, 11:53 AM
 Guest Join Date: Sep 2001 Location: Canada Posts: 2,098
Great! Glad it worked for you.

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     General Questions     Great Debates     Elections     Cafe Society     The Game Room     Thread Games     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit

All times are GMT -5. The time now is 04:23 PM.

 -- Straight Dope v3.7.3 -- Sultantheme's Responsive vB3-blue Contact Us - Straight Dope Homepage - Archive - Top
Copyright ©2000 - 2019, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@straightdope.com