MS Access 2000 - date calculations

[Barry Dwyer]

How can I calculate a week number from a date in wither
long date format or medium date format.
I want to do this in form based on a table. The user of
the form enters a date and I want the week number to
appear in the form and be saved in the table.


I also want to extract the month name and number from the
date entered by the user and want to save these in the
table also.


Can anyone help
thanks
Barty Dwyer, Brisbane, Australia

 

[Wayne Morgan]

It would probably be easiest to give the user multilple controls (textboxes,
comboboxes, etc) to enter the data then you can save it however you want.
Since there are a limited number of months in a year and days in a month,
combo boxes work well for that. Another option would be to use the calendar
control the comes with Access and let the use pick a date from that.

To get the week number from a date:

Format(Date, "ww")

will give the week for the current date. There are additional arguments for
the command for first week of the year and first day of the week.

Month Name:
MonthName(Month(Date))

Month Number:
Month(Date)

The Format command will also work for the above:
Today's date is 15 January 2004

Format(Date, "m") will return 1
Format(Date, "mm") will return 01
Format(Date, "mmm") will return Jan
Format(Date, "mmmm") will return January

 

[Van T. Dinh]

It is much more efficient to store the date rather than the different
components you mentioned. When you need to use the components, simply use
proper calculations to extract them. For example:

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

?Format(Date(), "m")
1

?DatePart("m", Date())
1

?Format(Date(), "mmmm")
January

Note that Format() returns a String (of digit characters in some cases
above) while DatePart() returns a Numeric.