weeknumber

  • Thread starter Thread starter Cor Roet
  • Start date Start date
C

Cor Roet

In VBA I get a wrong weeknumber for 29 december 2003:

Format(#12/28/03#, "ww", vbMonday, vbFirstFourDays) =52
Format(#12/29/03#, "ww", vbMonday, vbFirstFourDays) =53
Format(#12/30/03#, "ww", vbMonday, vbFirstFourDays) =1

Is this a known problem?
 
Hi,

Indeed, and that make statistic based on week completely screw up, since
week 53 will got 4 days only, and week 1 only 3 days. Consider, also, that
averages are influenced by the "extreme", having two weeks screw up out of
53 results, that makes the actual week numbering schema totally useless, for
stats. Unfortunately, Microsoft does not seem to be very well aware of the
problem. You know where their suggestion box is, isn't it?


One way to get around, if the ending day of a week is vbSaturday ( and
so, the starting day is vbSunday), is to compute the week number of the last
day of the week:

Dim offset As Long
offset=7-DatePart("w", MyDate)
weekNumber= DatePart("ww", offset+MyDate)
yearWeekNumber = DatePart("yyyy", offset+MyDate)


or, in a query:


SELECT DatePart("ww", 7-DatePart("w", MyDate) +MyDate) As weekNumber,
DatePart("yyyy", 7-DatePart("w", MyDate) +MyDate) As
YearOfWeekNumber,
SUM( whatever ) As StatForThatWeek

FROM somewhere

GROUP BY DatePart("ww", 7-DatePart("w", MyDate) +MyDate),
DatePart("yyyy", 7-DatePart("w", MyDate) +MyDate)



That does now work as well if the starting day is not vbSunday...


The problem is system (Windows) wide, not just limited to Access.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top