Getting Week Numbers in Access

July 7, 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?

Posted in Access Functions

Tags: Access Access Functions

You can follow any responses to this entry through the RSS 2.0 Feed feed. You can leave a response, or trackback from your own site.

10 Responses

Add your comment

Jon Peltier says:

July 7, 2009 at 3:15 pm

I'm waiting for someone to point out the difference between US and European definitions for Week Numbers.
Ron de Bruin says:

July 7, 2009 at 4:14 pm

Hi Jon

Why not

See
http://msdn.microsoft.com/en-us/library/bb277364.aspx
DataPig says:

July 7, 2009 at 5:17 pm

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

July 7, 2009 at 5:41 pm

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

DatePart("ww", Date, vbMonday, vbFirstFullWeek)
Ron de Bruin says:

July 7, 2009 at 6:47 pm

Hi Mike

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

See
http://support.microsoft.com/?kbid=200299
Ron de Bruin says:

July 8, 2009 at 5:26 pm

Submiting this test procedure to prove out the bug:

PLAIN TEXT
Visual Basic:
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
Jeff Coulson says:

September 4, 2009 at 4:33 am

In Access:

Format(Date(),"ww")

Format ([YourField],"ww")

Clng(Format(Date(),"ww"))
Henry says:

September 4, 2009 at 8:37 am

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
Jan-Willem Borst says:

November 12, 2009 at 10:17 am

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.