First and last day of week (1 Viewer)

Coxylaad

New member
Local time
Today, 20:33
Joined
Jan 11, 2022
Messages
21
Hi All,
Im building a date dimension table with all sorts of values in for a given date. one of the things I was to build is for a given date, I want to be able to reference the first and last day of the week.
My working week goes from Monday to sunday.

Ive wrote a little module to update the values and its picking the correct week start and end dates, apart from on a sunday where its putting that sunday in the next week. its got me scratching my head somewhat.

Heres my module:

strqry = "select * from dim_date where date < #01/01/1996# order by date"
Set rst = CurrentDb.OpenRecordset(strqry)

Do While rst.EOF = False
strtestdate = rst("date")

strWeekstart = rst("date") - (Weekday(rst("date"), vbMonday) - 1)
strWeekEnd = rst("date") - Weekday(rst("date"), vbMonday) + 7

'WeekNumber = rst("weekofyear")
'dayofweek = DatePart("d", rst("date"), vbMonday, vbFirstFourDays)
'dayofweek = Format(rst("date"), "dddd", vbMonday, vbFirstFourDays)
'newweekofyear = DatePart("ww", rst("date"), vbMonday, vbFirstFourDays)

With rst
.Edit
![firstdayofweek] = strWeekstart
![lastdayofweek] = strWeekEnd
.Update
.MoveNext
End With

Loop


So running this code works defines the start and end dates of the weeks correctly, however gets every sunday and puts it in the following week, ie it thinks sunday is the first day of the week, when it isnt.

Am I missing something or is this a fundamental flaw in the function?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:33
Joined
May 7, 2009
Messages
16,843
Code:
Public Function WeekFirstDay(ByVal dtVal As Date, Optional ByVal FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
    WeekFirstDay = dtVal - Weekday(dtVal, FirstDayOfWeek) + 1
End Function

?WeekFirstDay(Date(), vbMonday)
Result: Mar 4, 2022
 

Coxylaad

New member
Local time
Today, 20:33
Joined
Jan 11, 2022
Messages
21
yes that is correct, now instead of using todays date, try using this sunday 10/04/2022

It puts the sunday in the wrong week
 

Coxylaad

New member
Local time
Today, 20:33
Joined
Jan 11, 2022
Messages
21
Oh my god. so sorry, thats working, its the weekday field thats a day out!

Well that was embarrasing!
 

GPGeorge

Grover Park George
Local time
Today, 12:33
Joined
Nov 25, 2004
Messages
783
Learning is not embarrassing. Insisting one was right even after learning the opposite, now that would have been embarrassing.
 

Coxylaad

New member
Local time
Today, 20:33
Joined
Jan 11, 2022
Messages
21
very true. Im really loving developing in access, its so easy to get on with
 

Users who are viewing this thread

Top Bottom