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    
post Jan 24 2020, 10:07 AM

Posts: 257
Joined: 31-December 02

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

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())

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.


Go to the top of the page
post Jan 24 2020, 10:21 AM

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
post Jan 24 2020, 10:36 AM

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

Look at the documentation of the DatePart function:
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
post Jan 25 2020, 12:22 PM

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

Use DateDiff for comparisons:

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

or, in a query where the constants are unknown:
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
post Jan 29 2020, 05:51 AM

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