Week 53 and Week 1 from a given date

[MrMackey via AccessMonster.com]

In a regualar calendar week 53 for 2009 is defined as Dec 27-Dec 02. Week 1
is defined as Jan 03 - Jan 09.

It seems to be impossible to get Access VBA to return the correct week for a
date in this period no matter how the last parameter (firstweekofyear) is set.
Both Format() and DatePart have this problem. If one of the sample dates work
below, then the other does not. Does anyone have a solution?

'******* CODE BEGIN **************
Public Sub TESTING()

Dim D As Date
Dim O As Integer

'D = #12/30/2009# ' should return 53
D = #1/6/2010# ' should return 1
O = DatePart("ww", D, vbSunday, 0)
MsgBox O
End Sub
'******* CODE END**************

Mr. Mackey

 

[Tom van Stiphout]

On Fri, 11 Dec 2009 23:31:48 GMT, "MrMackey via AccessMonster.com"

One of my favorite topics.
There is no "regular calendar week", only "my opinion on what a
regular calendar week would be". For example in Outlook you can
specify how it should count week numbers, and specifically what week 1
is.
Then there is also a longstanding bug in oleaut32.dll that will not be
fixed: support.microsoft.com/kb/200299

-Tom.
Microsoft Access MVP

 

[ChrisO]

The default in Access states that Friday 1st January 2010 falls in week 1.
Since the week is defined as starting on vbSunday then all dates within the
range of, and including, Sunday 27th December 2009 to Saturday 2nd January
2010 are also in week 1.

There is no week 53.

http://support.microsoft.com/kb/200299