[AccessD] 53 Work weeks in 2007?

You da man. Thanks!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, January 02, 2007 1:54 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] 53 Work weeks in 2007?

Hi John

Wow. Just realised that this demonstrates on old bug in Access. That week
number is not 53 but 1!

Several years ago I wrote a function which does is right, and this is a
perfect moment to republish it.
I guess Access 2007 sports the same bug (I don't have A2007) as Microsoft
doesn't care for such subtle details.
Here goes:

Public Function ISO_WeekNumber( _
  ByVal datDate As Date) _
  As Byte

' Calculates and returns week number for date datDate according to the ISO
8601:1988 standard.
' 1998-2000, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  
  Dim bytWeek                   As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  
  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = WeekDay(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(Year(datDate), 12, 31)
    If WeekDay(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000 bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If
  
  ISO_WeekNumber = bytWeek

End Function

/gustav

>>> Gustav at cactus.dk 02-01-2007 19:36 >>>
Hi John

Yes, you miss the last parameter:

? Format(#12/31/2007#, "ww", vbMonday ,vbFirstFourDays)
53

which is very important when working with ISO 8601:1988 week numbers.

/gustav

>>> askolits at ot.com 02-01-2007 19:06 >>>
Last year I was using the following to get the week number:

WorkWeek = Val(Format("1/2/06", "ww", vbMonday)) - 1 )

The work week for my customer started on a Monday 

I never really understood why I had to add the "minus one", but the first
Monday and remaining week of 2006 would return a "2", so I just subtracted 1
and all was fine.

But this year, there are 53 Mondays so this line of code returns a "0" for
the first week of January.

Is there another way I should be getting the work week number?

John 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com