Getting Week Numbers in Access

It’s always a little embarrassing when you roll your own Function to achieve some functionality that already exists in Excel or Access. 

Recently, I stumbled across a user-defined function designed to find a week number in Access.  I asked the owner of this code why he wrote a function to do this when Access has one.  He gave me the ‘thousand-yard stare’, then he went on to explain that he couldn’t find the WEEKNUM function in Access.

In Excel, the WEEKNUM function converts a given date into a week number.  For example, this would give you the week number in which the current date falls. 

=WEEKNUM(TODAY())

There is no such function in Access.  You won’t find WEEKNUM in the list of available Access Functions.  There’s is WEEKDAY and WEEKDAYNAME, but no WEEKNUM.

So he naturally started to write his own function. 

Well, it turns out you can get to a week number in Access  by using the DATEPART function.  Like so:

DatePart(“ww”,Date())

You can also use this in Excel VBA to get around calling Application.WorksheetFunction.WeekNum

Today, my friend still uses his own Week Number user-defined function  – mainly out of spite. I suggested that he create a function that returns the average of range of numbers.  He told me to shut up.

Have you ever rolled your own user-defined function only to find out that the functionality already existed?

15 thoughts on “Getting Week Numbers in Access

  1. Ron de Bruin

    Submiting this test procedure to prove out the bug:

    [VB]

    Sub Test2()
    Dim GL_Dt As Date
    Dim I As Long

    For I = 39814 To 100000 ‘ test for more then 27 years
    GL_Dt = I
    If Int(([GL_Dt] – DateSerial(Year([GL_Dt] – Weekday([GL_Dt] – 1) + 4), 1, 3) + Weekday(DateSerial(Year([GL_Dt] – Weekday([GL_Dt] – 1) + 4), 1, 3)) + 5) / 7) _
    <> DatePart(“ww”, [GL_Dt], vbMonday, vbFirstFourDays) Then MsgBox “Error in date ” & I
    Next I
    End Sub

    Public Function IsoWeekNumber(d1 As Date) As Integer
    ‘ Attributed to Daniel Maher
    Dim d2 As Long
    d2 = DateSerial(Year(d1 – Weekday(d1 – 1) + 4), 1, 3)
    IsoWeekNumber = Int((d1 – d2 + Weekday(d2) + 5) / 7)
    End Function

    [/VB]

  2. Jan-Willem Borst

    I can hardly call this a PowerTip. A good function in Access for ISO (european) weeknumbers and the year that goes with it, that would be a PowerTip.

  3. cincin

    hi,

    sorry for the novice question, but to which area is the format applied?
    In Access:

    Format(Date(),”ww”)

    Format ([YourField],”ww”)

    Clng(Format(Date(),”ww”))

    thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>