Coxylaad
New member
- Local time
- Today, 20:52
- 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?
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?