Question:

This is strange.
I have a report that’s based on Sales.
The report has Grouping on Date.
The  Group On property is set to Week.
The report is from Friday 12/1/2006 through Sunday 12/31/2006.
The underlying table has sales in every weekday of the month.
There is no problem with the grouping – the report shows and counts the sales correctly in the appropriate week.

The problem is that the bound Date textbox should show the first sale in that week, but it’s showing a random date in that Week.

The Date textbox in the 1st week is showing Friday 12/1 as it should.

The first sale in the 2nd week of December was on 12/4 (Monday), but the Date textbox is showing 12/5
(true, there were sales on Tuesday 12/5, but there WERE sales on Monday 12/4)

The Date textbox in the 3rd week is showing Monday 12/11 as it should.

The first sale in the 4th week of December was on 12/18 (Monday), but the Date textbox is showing 12/19
(true, there were sales on Tuesday 12/19, but there WERE sales on Monday 12/18)

The first sale in the 4th week of December was on 12/25 (Monday), but the Date textbox is showing 12/26
(true, there were sales on Tuesday 12/26, but there WERE sales on Monday 12/25)

Here is the SQL of the query that the report is bound to:

SELECT tblSales.fldSaleDate, tblSales.fldSalesman, Sum(tblSales.fld679_Sold) AS SumOf679_Sold,
Sum(tblSales.fld679_Restocked) AS SumOf679_Restocked, Sum(tblSales.fld817_Sold) AS SumOf817_Sold,
Sum(tblSales.fld817_Restocked) AS SumOf817_Restocked, Sum(tblSales.fld100_Sold) AS SumOf100_Sold,
Sum(tblSales.fld100_Restocked) AS SumOf100_Restocked, Sum(tblSales.fld275_Sold) AS SumOf275_Sold,
Sum(tblSales.fld275_Restocked) AS SumOf275_Restocked
FROM tblSales
GROUP BY tblSales.fldSaleDate, tblSales.fldSalesman
ORDER BY tblSales.fldSaleDate;

Does anyone know what’s going on?
How can I get the first day of the week to appear in the bound date textbox (without hiding the bound date textbox and writing code to force the Monday in an unbound textbox)?

Solution:

Hello DavidWare

When you create a grouping in a report, it will indeed override any ordering in the source table or query. What’s more, you have not specified how you want your data sorted within each group, so you let the JetEngine use the quickest method, which is just how they arrive.

Try to display the detail section with the detailed sales, and you will see what I mean.

The trick is to add a second sorting expression, really just the same field, but not grouped by weeks. The same thing happens if you print a wordlist grouped by their first character. You then need a second sorting to sort within each letter group.

Once this is done, the group header will see the first detail information (the first sale in the group), while the group footer will see the last detail.

Good luck!
(°v°)