Hi Happy New Year to everyone
I have added a field into a table called "week numbers" which I want to show the week number through the year, starting at "1" for week beginning 1st January, is there any way this can be done automatically through Access/VBA. I need to be able to look at the date in another field and then update the week number field
Regards
Peter

Here is a simple script to demonstrate the syntax of what you want:

dtDate = Date()
iWeek = DatePart("ww", dtDate)
WScript.Echo iWeek

The important part is to take your date, and then use DatePart() on it using ww for work week. Today this script returned 1, since it is the first week of the year.

Does this help?


mmcdonal

Hi, Peter. If you have the date stored in another field then you do NOT want to store the week number. Calculated fields should never be stored; rather, they should be recalculated on the fly for your forms and reports. Storing calculated fields inflates your MDB unnecessarily and it creates the probablity of obsolete data if other values used to calculate them change.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division