Week Number

[Steve F]

I wish to obtain the week number for a given date using Access 2000 VBA.
When using the WEEKNUM function i.e. Test = WEEKNUM("22/11/2003",1) a compile error Sub or Function not defined appears. I have checked the ddl has been installed and can not establish why this function does not work.
Any help will be appreciated.

 

[John Spencer (MVP)]

WeekNum is not an ACCESS VBA function. You can try using DatePart to get what
you seek.

DatePart("ww",#11/22/2003#)

Note that I put your date in as MM/dd/yyyy. You MAY have to put the date in
that order to get it to return a correct value.

 

[Al Camp]

Steve,
I don't recognize WeekNum as an Access VBA function. Would this be
something you've used in Excel?
Try using the DatePart function... DatePart("ww",[YourDateField])
--
HTH...
Al Campagna
Candia Computer Consulting
Candia, NH

I wish to obtain the week number for a given date using Access 2000 VBA.
When using the WEEKNUM function i.e. Test = WEEKNUM("22/11/2003",1) a

compile error Sub or Function not defined appears. I have checked the ddl
has been installed and can not establish why this function does not work.

 

[Mike Sherrill]

I wish to obtain the week number for a given date using Access 2000 VBA.

This is the most generally reliable VBA expression for determining the
week number according to ISO standards. Specify all the optional
parameters to make sure user settings don't trip you up.

CInt(Format(TheDate, "ww", vbMonday, vbFirstFourDays))

Unfortunately, a bug makes Format() return the wrong value for some
days; 29 Dec 2003 is the next one to come. A description of the bug
is in KB article Q200299, "Format or DatePart Functions Can Return
Wrong Week Number". That article includes a function that works
correctly (according to ISO 8601).

Using a table instead of a function might make your application more
useful. A table containing the week numbers for every day for the
next 100 years would have less than 37,000 rows. It can be used
directly by any client software, not just Access. And using a table
makes it dead simple to determine the dates covered by a given week
number.