We help IT Professionals succeed at work.

Get in Access the week number from a given date

Gilberto Sanches
Gilberto Sanches asked
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

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

Untitled.jpg
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])
Gilberto SanchesFreelance ICT Consultant

Author

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