How to calculate the week number in MS Access?

[Jesse]

Dear Customer Service:

I know that I can use the function Weeknum in Excell to
calculate the week number for a date. But I could not
find the same function in MS Access 2000. How can I get
the build-in function for MS Access 2000 to calculate the
week number for a date?

Sincerely,

Jesse Huang

 

[Jeff Conrad]

Thank you for choosing to use the Microsoft Public Newsgroup for Access.
We hope we can assist you.

Both of these functions from the Immediate Window yield 39 which is the current week number:

?Format(Date(),"ww")
39
?DatePart("ww",Date())
39

We hope this helps.
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tom Wickerath]

Hi Jesse,

Check out the DatePart function in Access. You can specify the string "ww" for the interval to
get a count of the weeks. You can also include optional parameters that specify the
FirstDayOfWeek (default is Sunday if not included) and FirstWeekOfYear (the one in which January
1 occurs, if not included).

Syntax
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

An example of using in a query, with an alias "WeekNum", for a field named "RDate":

WeekNum: DatePart("ww",[RDate])

Note: I recommend that you do not use the field name "Date", as this is a reserved word in
Microsoft Access.


Tom
______________________________________________
Dear Customer Service:

I know that I can use the function Weeknum in Excell to
calculate the week number for a date. But I could not
find the same function in MS Access 2000. How can I get
the build-in function for MS Access 2000 to calculate the
week number for a date?

Sincerely,

Jesse Huang