Week Number

  • Thread starter Thread starter hughess7
  • Start date Start date
H

hughess7

Hi all

I've had a report working in Access for years using week number, this year
with the 1st of 2010 being a Friday it has thrown it out, it thinks Mon 4th
is week2. I know how to change this in my report to get it to display week
one - but is there a function or something I could create to ensure this is
correct every year without me having to manually edit my report?

The report is based on a crosstab query and displays 14 weeks at a time. The
calc for week number is done in an unbound column on the report itself,
repeated for each week (ie 14 times). An example follows:

Last year it was...
=Format([MonWk1],"mmmm") & " wk " & DatePart("ww",[MonWk1],1,1)

Which for this year it displays the 4th as Week 2 and I've had to change it
to:
=Format([MonWk1],"mmmm") & " wk " & DatePart("ww",[MonWk1],1,2)
to correctly display Week 1 instead.

Thanks
Sue
 
The functions that let you calculate Week Number (DatePart, Format) both
take an optional argument firstweekofyear which can have the following
values:

Constant Value Description

vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs
(default).
vbFirstFourDays 2 Start with the first week that has at least four
days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.

Try playing with that to get the value you want.
 
I originally wrote this in A97 but works fine up through A2003 at least.

Function WeekofYear(TheDate As Date)
Dim strYearPart As String
Dim strWeekPart As String
If Format(TheDate, "ww", 7, 2) = "53" And Year(TheDate) <>
Year(TheDate - 7) Then
strYearPart = CStr(CInt(Year(TheDate) - 1))
Else
strYearPart = Format(TheDate, "yyyy", 7, 2)
End If
strWeekPart = IIf(Len(Format(TheDate, "ww", 7, 2)) = 1, "0" _
& Format(TheDate, "ww", 7, 2), Format(TheDate, "ww", 7, 2))
WeekofYear = strYearPart & "-" & strWeekPart
End Function

Regards

Kevin
 
Back
Top