How to Group on and Display WeekEnding Dates?

[ElBozo]

Hello - in a report, how do i group on a date control (saledate) to
produce totals for each week-ending date in a year - to look something like:

Weekending dates Qty TotalDollars
---------------- ---- ------------
Sunday, May 1, 2005 23 $9300
Sunday, May 7, 2005 47 $12100
Sunday, May 14, 2005 29 $11000

and so on for the year.

I have played around with the "sorting/grouping" by selecting the
"saledate" and choosing "Week" from the "Group On" property - but this
does not group by the weekending dates for each Sunday, some are Monday
and some other weeks are even skipped over.

thanks
Baz

 

[Marshall Barton]

Hello - in a report, how do i group on a date control (saledate) to
produce totals for each week-ending date in a year - to look something like:

Weekending dates Qty TotalDollars
---------------- ---- ------------
Sunday, May 1, 2005 23 $9300
Sunday, May 7, 2005 47 $12100
Sunday, May 14, 2005 29 $11000

and so on for the year.

I have played around with the "sorting/grouping" by selecting the
"saledate" and choosing "Week" from the "Group On" property - but this
does not group by the weekending dates for each Sunday, some are Monday
and some other weeks are even skipped over.

Add a calculated firld to the report's record source query:

WeekEnding: DateAdd("d", 7 - DatePart("w", SaleDate, 2),
SaleDate)

The use the WeekEnding field instead of the SaleDate field.