Part III: More-Advanced Access Techniques
714
FIGURE 20.6
rptSalesJanuaryAlpha2 in Design view
Notice the CompanyName group header that was added by the Group Header setting in the
Group, Sort, and Total dialog box. The Property Sheet for the unbound text box is shown so you
can see the expression used to fill the text box.
This little trick works because all the rows within a CompanyName group have the first character
in common. Using the Left$() function to peel off the first character and use it as the text in the
text box in the group header provides an attractive, useful heading for the CompanyName groups.
Grouping on date intervals
Many reports require grouping on dates or date intervals (day, week, or month). For example,
Northwind Traders may want a report of January sales grouped on a weekly basis so that week-to-
week patterns emerge.
Fortunately, the Access report engine includes just such a feature. An option in the Group, Sort,
and Total dialog box enables you to quickly and easily group report data based on dates or date
intervals. Just as I grouped data based on prefix characters in an earlier example, I can group on
dates using the group’s GroupOn property. Figure 20.7 shows the January sales report grouped by
each week during the month. This report is named rptSalesJanuaryByWeek.
This report is easy to set up. Open the Group, Sort, and Total dialog box again and establish a
group for the OrderDate field. Set the OrderDate GroupHeader option to Yes and drop
down the Group On list (shown in Figure 20.8). Notice that Access is smart enough to present
Group On options (Year, Qtr, Month, Week, and so on) that make sense for date/time fields like
OrderDate. Selecting Week from this list instructs Access to sort the data on the OrderDate,
grouped on a week-by-week basis. Note, though, that you still need to sort by the entire value of
the OrderDate to ensure that they’re in sequential order within the week.
