Dcount week, starting Monday instead of Sunday

[Megaduck]

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.

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

Any ideas?

Thanks!

 

[Gasman]

DatePart function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

I also would not use the name Date for any of your fields?

 

[plog]

Subtract 1 day from [Date]

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

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

 

[plog]

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)

 

[Pat Hartman]

The DatePart function lets you set the week start day.

DatePart function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[Megaduck]

This seems to have worked for what I need:

=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]

This seems to have worked for what I need:

=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]

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]

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.

 

[onur_can]

You can find detailed explanation on the link.
DatePart Detail