Can't get Proper Week Start Date

TheSearcher

Registered User.
Local time
Today, 18:04
Joined
Jul 21, 2011
Messages
358
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:

Code:
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?

TheYearTheWeekNoTheDateTheDayTheWeekStart
202711/4/2027Monday1/4/2027
202711/5/2027Tuesday1/4/2027
202711/6/2027Wednesday1/4/2027
202711/7/2027Thursday1/4/2027
202711/8/2027Friday1/4/2027
202711/9/2027Saturday1/4/2027
202711/10/2027Sunday1/11/2027 - THIS IS WRONG
202721/11/2027Monday1/11/2027

Thanks in advance,
TS
 
Weekday(#01-01-1980#,vbMonday)

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

HTH:D
 
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.
 
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.
 
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.
 
If you use DateAdd in a query you could try...
Code:
DateAdd("d", -Weekday([TheDate], 2), [TheDate]) + 1
... and as a VBA function ...
Rich (BB code):
Function GetPreviousMonday(d1 As Date) As Date
'   returns the Monday equal or prior to d1
    GetPreviousMonday = d1 - Weekday(d1, vbMonday) + 1
End Function
 
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.

1663950949682.png


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
 
If you use DateAdd in a query you could try...
Code:
DateAdd("d", -Weekday([TheDate], 2), [TheDate]) + 1
... and as a VBA function ...
Rich (BB code):
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.
 
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
 
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)?
 

Users who are viewing this thread

Back
Top Bottom