Getting Week Numbers in Access

July 7th, 2009 by datapig Leave a reply »

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?

Advertisement

13 comments

  1. Jon Peltier says:

    I’m waiting for someone to point out the difference between US and European definitions for Week Numbers.

  2. DataPig says:

    Ron: Interesting. I did not know there was an international impact using WEEKNUM. Good link.

  3. DataPig says:

    Ron: Wouldn’t this give you an ISO week number?

    DatePart(“ww”, Date, vbMonday, vbFirstFullWeek)

  4. Ron de Bruin says:

    Hi Mike

    There are a few bugs when you use this so this is not a option

    See
    http://support.microsoft.com/?kbid=200299

  5. Ron de Bruin says:

    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]

  6. Jeff Coulson says:

    In Access:

    Format(Date(),”ww”)

    Format ([YourField],”ww”)

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

  7. Henry says:

    This will not return the correct weeknumber for all days. Check out this KB article and you will understand why he told….(DATAPIG SAYS PLAY NICE HENRY).

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

  8. Jan-Willem Borst says:

    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.

  9. sitemap list says:

    Wow, many rather logical tips! I appreciate you crafting this posting and the remainder of your internet site is outstanding!

Leave a Reply