Formatting dates

[Malche]

I have a Table with 3 columns

Date WeekNumber Revenue

If I run an update query using the function
format(date,"ww")
to update the field WeekNumber it works great, but, this
always gives me Saturday as the WeekEnding period. How do
I get the above function to give me Sunday as the
WeekEnding period.
Are there any system setups that will make vbSunday as
day 7 instead of being day 1?
I am using Access 2002
I would be realy greatfull for an easy solution to the
above. ----Malche

 

[Steve Schapel]

Malche,

The Format() function has a "first day of week" argument. To designate
a week beginning Monday, use it like this...
Format([Date],"ww",2)

However, why do you want to do this? It seems to flout a basic database
design principle. The WeekNumber is directly derivable from the Date,
and as such should not be separately stored in your table. This is the
kind of thing that sometimes happens in spreadsheets and stuff like
that, but it is not really applicable in a database. I suggest removing
the WeekNumber entirely from your table. You can use the calculation
whenever you need the WeekNumber for your purposes on form or report,
either in the Control Source of an unbound textbox on the form or report
itself, or within the query that the form or report is based on.

Another point, as an aside... the word Date has a special meaning in
Access, it is called a 'reserved word'. As such, it should not be used
as the name of a field or control or database object.

 

[Guest]

Steve
I have managed to get my replies working, the firewall
was stopping them.
Your solution works just fine and I take your note about
the way the database should be set up, I will work on it.
Thanks for your help and support.
Malche

-----Original Message-----
Malche,

The Format() function has a "first day of week" argument. To designate
a week beginning Monday, use it like this...
Format([Date],"ww",2)

However, why do you want to do this? It seems to flout a basic database
design principle. The WeekNumber is directly derivable from the Date,
and as such should not be separately stored in your table. This is the
kind of thing that sometimes happens in spreadsheets and stuff like
that, but it is not really applicable in a database. I suggest removing
the WeekNumber entirely from your table. You can use the calculation
whenever you need the WeekNumber for your purposes on form or report,
either in the Control Source of an unbound textbox on the form or report
itself, or within the query that the form or report is based on.

Another point, as an aside... the word Date has a special meaning in
Access, it is called a 'reserved word'. As such, it should not be used
as the name of a field or control or database object.

--
Steve Schapel, Microsoft Access MVP

I have a Table with 3 columns

Date WeekNumber Revenue

If I run an update query using the function
format(date,"ww")
to update the field WeekNumber it works great, but, this
always gives me Saturday as the WeekEnding period. How do
I get the above function to give me Sunday as the
WeekEnding period.
Are there any system setups that will make vbSunday as
day 7 instead of being day 1?
I am using Access 2002
I would be realy greatfull for an easy solution to the
above. ----Malche

.