Reply
 
Thread Tools Display Modes
  #1  
Old 08-13-2019, 11:19 AM
Spud's Avatar
Spud is offline
Guest
 
Join Date: Jul 1999
Location: Central Indiana
Posts: 3,929

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  
Old 08-13-2019, 11:42 AM
borschevsky is offline
Guest
 
Join Date: Sep 2001
Location: Canada
Posts: 2,101
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 View Post
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  
Old 08-13-2019, 12:02 PM
Spud's Avatar
Spud is offline
Guest
 
Join Date: Jul 1999
Location: Central Indiana
Posts: 3,929
Quote:
Originally Posted by borschevsky View Post
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  
Old 08-14-2019, 10:03 AM
Spud's Avatar
Spud is offline
Guest
 
Join Date: Jul 1999
Location: Central Indiana
Posts: 3,929
It worked perfectly... Thank you!
  #5  
Old 08-14-2019, 11:53 AM
borschevsky is offline
Guest
 
Join Date: Sep 2001
Location: Canada
Posts: 2,101
Great! Glad it worked for you.
Reply

Bookmarks

Thread Tools
Display Modes

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 Jump


All times are GMT -5. The time now is 02:49 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.

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

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Copyright 2018 STM Reader, LLC.

 
Copyright © 2017