Dcount week, starting Monday instead of Sunday (1 Viewer)

Megaduck

Member
Local time
Today, 06:42
Joined
Apr 24, 2020
Messages
30
Hello,

I have the following Dcount query to display weekly record count. Access assumes the week starts on Sunday. I would like to, somehow, adjust this code to count from Monday instead. I thought it would be as easy as adjusting a setting in Options to start weeks on Monday but apparently that isn't an option. :(

Code:
=DCount("*","[Truck Log]","DatePart('ww',[Date]) = DatePart('ww',Date()) And Year([Date]) = Year(Date())")

Any ideas?

Thanks!
 

plog

Banishment Pending
Local time
Today, 05:42
Joined
May 11, 2011
Messages
11,613
Subtract 1 day from [Date]


Or maybe Subtract 1 day from Date(). Definitely subtract 1 day from one of those dates.
 

plog

Banishment Pending
Local time
Today, 05:42
Joined
May 11, 2011
Messages
11,613
The more I thought about what date to subtract 1 from, the more I got confused. What week and year do you expect the following dates to match with:

January 1 2017 (Sunday)
January 1 2018 (Monday)
January 1 2020 (Wednesday)
 

Megaduck

Member
Local time
Today, 06:42
Joined
Apr 24, 2020
Messages
30
This seems to have worked for what I need:
Code:
=DCount("*","[Truck Log]","DatePart('ww',[Date],2) = DatePart('ww',Date()) And Year([Date]) = Year(Date())")

Thanks for stirring me in the right direction. Haha.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:42
Joined
Sep 21, 2011
Messages
14,050
This seems to have worked for what I need:
Code:
=DCount("*","[Truck Log]","DatePart('ww',[Date],2) = DatePart('ww',Date()) And Year([Date]) = Year(Date())")

Thanks for stirring me in the right direction. Haha.
I would have thought you would need the third parameter of DatePart in both functions?
 

Megaduck

Member
Local time
Today, 06:42
Joined
Apr 24, 2020
Messages
30
I would have thought you would need the third parameter of DatePart in both functions?
I was thinking it may be necessary, too, but I tried it there first and it subtracted the records from Sunday off the week's count. No idea... lol.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:42
Joined
Feb 19, 2002
Messages
42,981
You should test very carefully. I would use the the third argument in BOTH uses of the function rather than taking a chance. In addition to being sloppy, it will confuse anyone who looks at the code later.
 

Users who are viewing this thread

Top Bottom