We help IT Professionals succeed at work.

# Get in Access the week number from a given date

Hey experts,

In Access 2016, I didn't get the weeknumber with function WeekDayName or Weekday.

How can I Get the week number in a column from another column which has the given date?
Comment
Watch Question

## View Solution Only

Top Expert 2014
just an additional comment, try use DatePart function in the Control Source directly.

Most Valuable Expert 2015
Distinguished Expert 2018

Note please, that European week numbering in Access is buggy.

Use a function like this:

```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```

Then, as ControlSource for your week number textbox, use:

`=ISO_WeekNumber([YourDateField])`
Freelance ICT Consultant

Commented:
I used the function DatePart in a Select query, it works as expected. Thanks Aikimark, Ryan & Gustav.