week number HELP

[Art Vandaley]

Hi,

In access 2007 Beta I have following problem:

text1.value = calendar1.value (text1's format is "ww")

text1 returns 53 for the date of 29.12.2006. Normally it must be 52

How can I fix this problem?

How can I use "firstweekof year" ?

PS: "Format(expression[, format[, firstdayofweek[, firstweekofyear]]])"
means nothing to me. I need more explanation. Where should I put the format
VB or properties?

Thanks a lot for your help in advance..

 

[Arvin Meyer [MVP]]

Try using this function instead:

Public Function WeekNum(dt As Date) As Variant
Dim wn As Variant
Dim period As Date
Dim yr As Long
Dim mth As Integer

yr = Year(dt)
mth = Month(dt)

If mth < 7 Then yr = yr - 1
period = "01-07-" & Format(yr, "####")

wn = DateDiff("ww", period, dt)

WeekNum = Format(Abs(wn) + 1, "00")
End Function

You may need to change the line:

period = "01-07-" & Format(yr, "####")

to:

period = "07-01-" & Format(yr, "####")

because of you regional settings.