Report Grouping on Week with Monday Start

[misscrf]

I have read so many threads on this I am dizzy, confused and want to cry. I have a report of time worked by person, and am calculating over time. When I run the report, I need the beginning of the week to be Monday, and the beginning of the data to start its run (if it starts on a Wednesday, then the first week only has data Wed-Sun for the first week of information).

I have tried a million different groupings, with date part, and more. Under the best scenario I get the data grouped by date, but the 7th day of any week goes over to the next weeks grouping.

Can anyone please help me?

Thanks!!!!!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You could group by the week based on your date minus 1 day.

[red] Format([DateField]-1,"ww")[/red]


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[BSman]

If you include a field in your query that defines the date for the week and group on that (and don't include the specific date) you should be able to do your calculations. The trick is to define the DateForWeek correctly based on your requirements that your week starts on Monday.

In the query designer, define a column like this (assuming ActualDate is your column that contains the actual work date):

DateForWeek: DateAdd("d",-1*(IIF(Day([ActualDate])<3,Day([ActualDate])+5,Day([ActualDate]-2)),[ActualDate])

This is based on Saturday returning a 1 for the day of the week and Friday returning a 5.

Bob

 

[misscrf]

Thank you all. The problem that I was having, was that the end of the year, if say 1/1 of the new year was on a saturday, it would cut off the week it was a part of and make it a new week by itself. My boss helped me make a calculated field in the query, that works like this:

CustomWeekYear: IIf((DatePart("m",[PunchDate])=1 And DatePart("WW",[PunchDate],2,3)=53) Or (DatePart("d",[PunchDate])=1 And DatePart("yyyy",[PunchDate],2,3)="2006"),DatePart("yyyy",[PunchDate],2,3)-1,DatePart("yyyy",[PunchDate],2,3)) & IIf(Len(DatePart("WW",[PunchDate],2,3))<2,"0" & DatePart("WW",[PunchDate],2,3),DatePart("WW",[PunchDate],2,3))

I found that 2006 was an extra issue because of where the first landed, so I had to deal with that by itself. I would have worked out a more consistent formula, but it was for a set of static data, and will not change. I just needed to get it done, so I didn't worry about making the formula work under any cirumstance, just the period of time I needed it to work.

Thank you all for your responses.


misscrf

It is never too late to become what you could have been ~ George Eliot