Report Grouping by Week 1

[mtnsurfer22]

I have read through many posts on this forum that are similar to my problem. However, it is now clear to me that I don't understand Access reporting as I cannot figure out a solution.

I have built a report using the wizard in Access. I have three criteria in my report...Date, MachineID, Hours. Each day the Hours are recorded from each machine into a table. The report I need will show the number of hours each machine has run each week (Monday through Sunday). I would like it to look as follows...

Monday, Jan 24th, 2005
Machine1
1/24/05 152
1/25/05 160
1/26/05 165
1/27/05 170
1/28/06 175
1/29/05 180
1/30/05 185
Summary:
Max: 185 Min: 152 Difference 33

Machine2
1/24/05 15
1/25/05 16
1/26/05 16
1/27/05 17
1/28/06 17
1/29/05 18
1/30/05 18
Summary:
Max: 18 Min: 15 Difference 3

Monday Jan 31, 2006
etc....etc...

The report is working as I have my three fields on the report and am grouping by date then by machine number. However, as you have probally guessed...I am sorting the date by week and therefore the first week is only 5 days as it then sets the first day of the week to Sunday for the rest of the report (I have a filter in place where the first date is a Monday so it beings there).

I have also tried to group the dates by 'Day' with an interval of 7...but for some reason that ends up making the first day of the week a Saturday after the first week..??

In the date text box in the Header of my Date grouping I have tried using =Format([Date],"ww",2,0) but it seems to have no affect. The only way I have found to change the grouping interval is by using the icon on the toolbar.

I am faimilar with VB and have written a fair amount of basic interfaces...but have never had to use it when writting reports.

 

[dhookom]

Apparently you only need to resolve how to group by week with weeks beginning Mondays. You can use an expression in the sorting and grouping or in the query like:
DateAdd("d",-Weekday([Date]-1)+2,[Date]-1)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtnsurfer22]

Thnaks so much for your post...(I realize now that I can write expressions in that sorting and grouping dialog box)...though I still don't have the syntax correct.

When I use your expression in the grouping box...It returns...

Syntax error in query expression 'Int((DateAdd("d",-Weekday([Date]-1)+2,[Date]-1)\7\1'

It appears that since I still have the sorting options selected in the bottom portion of the dialog that it is appending to my expression....though I'm still not sure what the extra syntax is trying to return.

 

[dhookom]

Did you change the interval in the sorting and grouping?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtnsurfer22]

If I change the interval number, the last digit in the error message simply changes to reflect that number. If I change to sort by 'Day' instead of 'week' the 7 simply goes away and there is only one \ and one number (the interval number) reflected in the string.

I'm still strugging to see how the "Int([Expression\Field]\'Criteria'\'Interval'" expression works to generate the proper grouping.

PS. I'm using Access 2000

 

[dhookom]

Who suggested you use "Int([Expression\Field]\'Criteria'\'Interval'"? It looks nothing like what I suggested.

The Group On should be set to Each Value with Group Interval set to 1.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtnsurfer22]

Thanks for your help, but it is still not working for me. I have set the Group On value to 'Each Value' and the Interval to 1. When I enter your expression...

DateAdd("d",-Weekday([Date]-1)+2,[Date]-1)

It returns a 'syntax error extra )' message.

I have tried this DateAdd expression a number of ways and continue to recieve the same error...

 

[mtnsurfer22]

I have found a temporary work-around that should work for now...

I have set the grouping for my [Date] field to Group On Week with an interval of 1. I then made sure that my report was using 'system settings' for the date grouping property. I then simply change the time zone of the server to the UK since the begining of their week is considered Monday which is what I need the first day of the week to be.

This works for now since this application is running on a terminal server and the time zone shouldn't affect any other applications.

 

[dhookom]

When you use an expression for sorting and grouping, you must prefix with "=":

=DateAdd("d",-Weekday([Date]-1)+2,[Date]-1)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mtnsurfer22]

I am a fool...(and some more sleep at night would help too)

It works great

 

[dhookom]

I should have included the "=" in my earlier replies...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]