week number from date

[Davepacey]

I live in UK, and where I work, the week starts on a Saturday, I have a form based on a table with several fields, four of which are "Date" "Week" "Month" and "Year"

When the user enters the date, the other three fields are filled automatically from the date using the following code, my problem is that if the date entered is the Saturday, the week number that is extracted is the previous week as Saturday starts our new week, and the system thinks Sunday is first day. I am sure that some of you will have an easy answer that I have missed, but I need help please. All four fields are needed for various different reporting functions. I need the automatic option to reduce the risk of error in Month etc. I searched for Firstdayofweek etc but can find nothing.

Private Sub Date_AfterUpdate()
Me![Year] = DatePart("yyyy", [date])
Me![week] = DatePart("ww", [date])
Me![Month] = Format([date], "mmmm")
End Sub

TIA

Dave

 

[Fornatian]

Does this help?

Function StartOfWeek(d As Variant, Optional FirstWeekday As Integer) As Variant
'
' Returns the date representing the first day of the current week.
'
' Arguments:
' D            = Date
' FirstWeekday = (Optional argument) Integer that represents the first
' day of the week (e.g., 1=Sun..7=Sat).
'
If IsMissing(FirstWeekday) Then 'Sunday is the assumed first day of week.
  StartOfWeek = d - WeekDay(d) + 1
Else
  StartOfWeek = d - WeekDay(d, FirstWeekday) + 1
End If
End Function

 

[GumbyD]

Try this:



If(Weekday([date])=7 then
if(datepart("y",[date])= 1 then
'this is January 1 so we need to set the week to 1
me![week] = 1
Else
me![week] = DatePart("ww",[Date])-1
End if
Else
me![week] = DatePart("ww",[date]))
End if

 

[Davepacey]

Thanks

GumbyD, I opted for your code, modified as below it works perfectly when I changed the -1 to +1
-1 was making it go to the previous week. i.e Friday 22/08/2003 is last day of week 34 your code as is made Saturday 23/08/2003 week 33 when it should be first day of week 35.

Thanks to both of you for your help.

Private Sub Date_AfterUpdate()

If (WeekDay([date])) = 7 Then
If (DatePart("y", [date])) = 1 Then
'this is January 1 so we need to set the week to 1
Me![week] = 1
Else
Me![week] = DatePart("ww", [date]) + 1
End If
Else
Me![week] = DatePart("ww", [date])
End If

Me![Month] = Format([date], "mmmm")
Me![Year] = DatePart("yyyy", [date])
End Sub

 

[Pat Hartman]

The DatePart() function allows you to specify which day a week starts on. Doesn't that do it for you?

 

[Davepacey]

I thank you all

Pat,
Whilst the code I used following help given to me earlier works fine, after reading your answer, I revisited the DatePart function, and yes, you are correct, all I needed was the code below. I must get a decent reference book, or read the online help more

Private Sub Date_AfterUpdate()

Me![week] = DatePart("ww", [date], vbSaturday, vbFirstJan1)
Me![Month] = Format([date], "mmmm")
Me![Year] = DatePart("yyyy", [date])
End Sub

I am grateful to you guys, many thanks.

Dave