MS Access - change start of week to Monday from Sunday (US)
Posted on 2014-09-16
Info you might need: in the US; Access 2010; program someone else wrote; I've only been doing this 6 months.
I'm trying to run queries (and build reports on them) for dates that fall into a "last week" time frame where the week runs Monday to Sunday.
90% of the time, this will be run on a Monday, but I don't want to use Between Date()-7 and Date()-1 because there are times when it might be pulled later in the week. I want the reports to be able to be pulled by clerical people by just pushing a button, not doing anything complicated.
I found this:
Year([FieldName])* 53 + DatePart("ww", [FieldName]) = Year(Date())* 53 + DatePart("ww", Date()) - 1
And it works like I want, except it returns Sunday to Saturday.
I thought changing my computer settings through control panel to have the week start Monday would help this (changing globally on my computer), but it's still returning Sunday to Saturday.
I've found a couple of things referencing vbMonday for first day of week in specific bits of code (primarily to return a day of the week for a date, but I'm not finding the correct syntax for what I'm looking for. I'd like to set it as a global constant so it covers the whole database.
Can I do this?