How to change the start day of the week on reports Access 2000

  • Thread starter Thread starter Michael Hernon
  • Start date Start date
M

Michael Hernon

Hi All,

I dont know if there is a FAQ part to the newsgroup that covers this
I have reports for my business that print out weekly figures.

I have 2 problems.
1. The weeks all start on a Monday, and my business runs from Sunday to
Saturday, is there an easy way to change the reports to get Sunday as the
first
day?

2. Week 1 of my business year is actually 30/11/03 (sunday), but access has
week 1 as 05/01/04(monday)

I have the field that produces the week number as
=Format$([Date],"ww",0,0)
if I change it to =Format$([Date]+32,"ww",0,0) this gets the week numbers
almost right but not quite.

Any help would be great.

Thanks,

Mick.
 
Michael said:
Hi All,

I dont know if there is a FAQ part to the newsgroup that covers this
I have reports for my business that print out weekly figures.

I have 2 problems.
1. The weeks all start on a Monday, and my business runs from Sunday to
Saturday, is there an easy way to change the reports to get Sunday as the
first
day?

2. Week 1 of my business year is actually 30/11/03 (sunday), but access has
week 1 as 05/01/04(monday)

I have the field that produces the week number as
=Format$([Date],"ww",0,0)
if I change it to =Format$([Date]+32,"ww",0,0) this gets the week numbers
almost right but not quite.
Click to expand...

I don't know how you define the first week of the year, but
you probably need to add some number of weeks so that
05/01/04 is not week 1.

To start the week on a Sunday use a 1 instead of the system
default 0.

This is kind of a stab in the dark, but maybe you want
something more like this:

=Format(((DatePart("ww", [Date], 1, 2) + 4) Mod 52) +1,"0")

Be sure to check Help for details on the firstdayofweek and
firstweekofyear arguments.
 
Cheers for the help,

Eventually decided to do it the hard way,

Created a table of every date in the year in one colum, what week no. that
date was in in the next colum
then added that as a table.

Set up a query for all dates in my invoices that matched the date in the new
table

Set up a report to group it into week numbers,

Thanks, but I could not got the report to move the Sunday as the first day

Mick


Marshall Barton said:
Michael said:
Hi All,

I dont know if there is a FAQ part to the newsgroup that covers this
I have reports for my business that print out weekly figures.

I have 2 problems.
1. The weeks all start on a Monday, and my business runs from Sunday to
Saturday, is there an easy way to change the reports to get Sunday as the
first
day?

2. Week 1 of my business year is actually 30/11/03 (sunday), but access has
week 1 as 05/01/04(monday)

I have the field that produces the week number as
=Format$([Date],"ww",0,0)
if I change it to =Format$([Date]+32,"ww",0,0) this gets the week numbers
almost right but not quite.
Click to expand...

I don't know how you define the first week of the year, but
you probably need to add some number of weeks so that
05/01/04 is not week 1.

To start the week on a Sunday use a 1 instead of the system
default 0.

This is kind of a stab in the dark, but maybe you want
something more like this:

=Format(((DatePart("ww", [Date], 1, 2) + 4) Mod 52) +1,"0")

Be sure to check Help for details on the firstdayofweek and
firstweekofyear arguments.
Click to expand...
 
Back
Top