Can't get Proper Week Start Date

[TheSearcher]

Our business week starts on a Monday. I am using the following query to return the year, the week number, the date, the day of the week and the week start date:

SELECT "2027" AS TheYear, DatePart("ww",[TheDate],2,2) AS TheWeekNo,
t_Calendar.TheDate,
t_Calendar.TheDay, DateAdd("d",-Weekday([theDate])+2,[theDate]) AS TheWeekStart
FROM t_Calendar;

The result is correct except for the TheWeekStart. How can I get it to recognize that the first day of the week is a Monday?

TheYear	TheWeekNo	TheDate	TheDay	TheWeekStart
2027	1	1/4/2027	Monday	1/4/2027
2027	1	1/5/2027	Tuesday	1/4/2027
2027	1	1/6/2027	Wednesday	1/4/2027
2027	1	1/7/2027	Thursday	1/4/2027
2027	1	1/8/2027	Friday	1/4/2027
2027	1	1/9/2027	Saturday	1/4/2027
2027	1	1/10/2027	Sunday	1/11/2027 - THIS IS WRONG
2027	2	1/11/2027	Monday	1/11/2027

Thanks in advance,
TS

 

[Guus2005]

Weekday(#01-01-1980#,vbMonday)

Tell weekday that monday is your first day of the week.
The default is vbSunday

HTH

 

[plog]

Just to make sure you're doing what you want (not the computer, you). The above example means either 1/3/2027 is in Week 53 of 2026 or Week 0 of 2027. That sound good? Just checking.

If the answer is "1/1 - 1/3 is a holiday and a weekend, so it doesn't matter", what happens when 1/1 is a Tuesday? That makes 1/7 the first day of your year and business days 1/2 - 1/4 occur in the prior year. Just a thought.

 

[TheSearcher]

Guus2005 - I don't understand what you mean. Your suggested code returns "3" when I run it.
Plog - Yes 1/3/2027 is week 53 of 2026.
I'm sure my query just needs a bit of tweaking but I can't seem to get it to recognize that Monday is the start of the week although I don't have that problem with the DatePart("ww",[TheDate],2,2) AS TheWeekNo part. I'm probably overlooking something obvious. But I can't seem to get it to work.

 

[sonic8]

Guus2005 - I don't understand what you mean. Your suggested code returns "3" when I run it.

I suggest you retry that!
Weekday(#01-01-1980#,vbMonday)
must return 2.
The FirstDayOfWeek argument is exactly what is required to make the function recognize Monday as the start of the week.

 

[MarkK]

If you use DateAdd in a query you could try...

DateAdd("d", -Weekday([TheDate], 2), [TheDate]) + 1

... and as a VBA function ...

Function GetPreviousMonday(d1 As Date) As Date
'   returns the Monday equal or prior to d1
    GetPreviousMonday = d1 - Weekday(d1, vbMonday) + 1
End Function

 

[TheSearcher]

I suggest you retry that!
Weekday(#01-01-1980#,vbMonday)
must return 2.
The FirstDayOfWeek argument is exactly what is required to make the function recognize Monday as the start of the week.

vbMonday is not recognized in query design. So, I replaced it with 1 (Monday). It returns 3.

TheDate Expr1
1/1/2030 3
1/2/2030 3
1/3/2030 3
1/4/2030 3
1/5/2030 3
1/6/2030 3
1/7/2030 3
1/8/2030 3

 

[theDBguy]

vbMonday is not recognized in query design. So, I replaced it with 1 (Monday).

Hi. Pardon me for jumping in, but I think 1 is Sunday. Monday is 2.

 

[TheSearcher]

If you use DateAdd in a query you could try...

DateAdd("d", -Weekday([TheDate], 2), [TheDate]) + 1

... and as a VBA function ...

Function GetPreviousMonday(d1 As Date) As Date
'   returns the Monday equal or prior to d1
    GetPreviousMonday = d1 - Weekday(d1, vbMonday) + 1
End Function

Mark - the query is perfect. Thanks! I already wrote a function that works and accomplished what I need but I knew there had to be a more efficient way.

 

[TheSearcher]

DBGuy - you are correct. My mistake. But then it returns 2.
TheDate Expr1
1/1/2030 2
1/2/2030 2
1/3/2030 2
1/4/2030 2
1/5/2030 2
1/6/2030 2
1/7/2030 2
1/8/2030 2

 

[theDBguy]

DBGuy - you are correct. My mistake. But then it returns 2.
TheDate Expr1
1/1/2030 2
1/2/2030 2
1/3/2030 2
1/4/2030 2
1/5/2030 2
1/6/2030 2
1/7/2030 2
1/8/2030 2

Probably because your Expr1 column contains Weekday(#1/1/1980#,2) instead of Weekday([TheDate],2)?