Grouping by Weeks, Changing Start of Week

[themurph2000]

I am running a database that records production. I have to group production by the date on a weekly basis. I have successfully done that, except for the fact that we have production running on the weekends as well. Our weeks should be grouped from Monday to Sunday, for example...

Sunday, September 23, 2007

Should be in the grouping

Week of September 17, 2007

and not the first day of the new week.

The Sorting and Grouping window doesn't seem to allow for this. Any ideas?

 

[Rabbie]

Why not add a Week Commencing field to the data source for your report. If you use this formula for this field it should give you what you are looking for.

WeekCommencing = ProductionDate - Weekday(ProductionDate,3)

 

[themurph2000]

Why not add a Week Commencing field to the data source for your report. If you use this formula for this field it should give you what you are looking for.

WeekCommencing = ProductionDate - Weekday(ProductionDate,3)

That works great except for one thing: If the starting day of that week is the same as the date in the transaction, the previous Monday is given.

EX: Transaction is for Monday, September 10th.

WeekCommencing will then equal Monday, September 3

I tried adding an IIF statement saying if your formula is the same as the Production Date then just use the Production Date. That, however, did not seem to work well.

 

[themurph2000]

That works great except for one thing: If the starting day of that week is the same as the date in the transaction, the previous Monday is given.

EX: Transaction is for Monday, September 10th.

WeekCommencing will then equal Monday, September 3

I tried adding an IIF statement saying if your formula is the same as the Production Date then just use the Production Date. That, however, did not seem to work well.

NEVER MIND. Sorry. I fixed it. Was not thinking right. All I had to do was see if Weekday[Production Date] returned the value of 2.

 

[Rabbie]

This formula also gives the correct date

WeekCommencing = ProductionDate - Weekday(ProductionDate,2) + 1

The other formula worked correctly in Excel but not in Access.

 

[themurph2000]

This formula also gives the correct date

WeekCommencing = ProductionDate - Weekday(ProductionDate,2) + 1

The other formula worked correctly in Excel but not in Access.

Oh, no problem. What you wrote, combined with the IIF statement, works great. Thanks.

 

[Rabbie]

Glad you got it to work. Happy to help.