Hi Apollo13a,
Your problem is caused by the fact that the default week starts on a Sunday; if you read back through the thread you will see how to group by Saturdays, but an explanation may help (along with my apologies for a fairly lengthy post).
As I said, the default week starts on a Sunday, so the Weekday Function returns the following numbers:
1 = Sunday
2 = Monday
:
:
7 = Saturday
.. and the expression Weekday([DateValue])-1 returns:
0 = Sunday
1 = Monday
:
:
6 = Saturday
Subtracting Weekday([DateValue])-1 from [DateValue], gives:
If [DateValue] is a Sunday, the Sunday date
If [DateValue] is a Monday, the date one day earlier, the Sunday date
:
:
If [DateValue] is a Saturday, the date six days earlier, the Sunday date
.. so Sunday through Saturday all return the same Sunday date and are thus grouped together.
Now, your change, subtracting Weekday([DateValue])-[highlight]0[/highlight] from [DateValue], gives:
If [DateValue] is a Sunday, the date one day earlier, the Saturday date
If [DateValue] is a Monday, the date two days earlier, the Saturday date
:
:
If [DateValue] is a Saturday, the date seven days earlier, the previous Saturday date
.. so Sunday through Saturday all return the same date again, only this time it's the Saturday date, and are thus grouped together just as before. The only difference is the (somewhat arbitrary) calculated date on which you are grouping.
What you want to do is use a different start of week, not just a different date based on it. You can do this by changing the calculation (as seen in earlier posts) or you can do it by overriding the start of week. It becomes a little clearer if we look at the original expression, and show the (default) week start in it:
SundayOfWeek: [DateField]-(Weekday([DateField])-1)
.. is the same as ..
SundayOfWeek: [DateField]-(Weekday([DateField],vbSunday)-1)
If we change the start of week to be Saturday, the values returned by the Weekday Function become:
1 = Saturday
2 = Sunday
:
:
7 = Friday
.. and the values returned by the expression Weekday - 1 become:
0 = Saturday
1 = Sunday
:
:
6 = Friday
Using this, the values returned by subtracting this from the DateValue, become:
If [DateValue] is a Sunday, the date one day earlier, the previous Saturday date
If [DateValue] is a Monday, the date two days earlier, the previous Saturday date
:
:
If [DateValue] is a Friday, the date six days earlier, the previous Saturday date
If [DateValue] is a Saturday, the date [highlight]no[/highlight] days earlier, the [highlight]current[/highlight] Saturday date
Now the grouping is based on a Saturday week start. The code for this is just like the full code for the Sunday version (above) with an explicit week start of Saturday:
[red]Saturday[/red]OfWeek: [DateField]-(Weekday([DateField][red],vbSaturday[/red])-1)
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.