UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Return Dates Within The Current Week But Week Starts On Sat's, Access 2010    
 
   
dadof3
post Jan 24 2020, 10:07 AM
Post#1



Posts: 257
Joined: 31-December 02



Microsoft Help tells me this expression will give me dates within the current week:

QUOTE
Returns items with dates during the current week. A week in Access starts on Sunday and ends on Saturday.

DatePart("ww", [SalesDate]) = DatePart("ww", Date()) and Year( [SalesDate]) = Year(Date())
https://support.office.com/en-us/article/ex...89-5fc961f21762

However, our weeks start on Sat (not Sun). Week = Sat thru Fri

*I'm wanting to show dates that fall WITHIN the CURRENT week we're in.

*And dates that fall AFTER the CURRENT week we're in.

Ideas?


Go to the top of the page
 
GroverParkGeorge
post Jan 24 2020, 10:21 AM
Post#2


UA Admin
Posts: 36,747
Joined: 20-June 02
From: Newcastle, WA


I wonder if you have set your regional settings on your computer accordingly?
Attached File  regionalsettings.png ( 57.61K )Number of downloads: 2

Attached File  firstdayofweek.png ( 21.44K )Number of downloads: 0

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PhilS
post Jan 24 2020, 10:36 AM
Post#3



Posts: 679
Joined: 26-May 15
From: The middle of Germany


Look at the documentation of the DatePart function:
QUOTE
DatePart(interval, date, [ firstdayofweek, [ firstweekofyear ]])
[...]
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
Gustav
post Jan 25 2020, 12:22 PM
Post#4


UtterAccess VIP
Posts: 2,173
Joined: 21-February 07
From: Copenhagen


Use DateDiff for comparisons:

For the current week:
CODE
DateDiff("ww", SalesDate, Date, vbSaturday, vbUseSystem) = 0

or, in a query where the constants are unknown:
CODE
DateDiff("ww", [SalesDate], Date(), 7, 0) = 0

To find matches for the next week, don't compare to 0 but to -1.
Go to the top of the page
 
dadof3
post Jan 29 2020, 05:51 AM
Post#5



Posts: 257
Joined: 31-December 02



The code for query you provided is exactly what I am looking for, thank you! However I would be running this everyday and when I get to the middle of a week, let's say Wednesday, I'd like the days prior to Wednesday fall off as part of the current week since we're pass those days?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2020 - 02:56 PM