Group records on intervals of date or time values on a data access page

Group records on intervals of date or time values on a data access page

Learn about grouping records by month.

Create the grouped report and set the grouping interval

  1. Open the data access page in Design view.

  2. Create a grouped data access page.

  3. Make sure the control you want to use to group by date or time interval is selected.

  4. Click Promote on the toolbar.

    Microsoft Access adds a group header that contains an expand control, and a record navigation section that contains a record navigation control. Access also moves the control you grouped on to the group header.

  5. Click Sorting And Grouping on the toolbar to display the Sorting And Grouping box.

  6. Click the group record source that you want to group by date or time interval.

  7. In the GroupOn property, click a value in the list. You can group records on values that fall in the same calendar year, calendar quarter, month, week, day, hour, or minute.

  8. Set the GroupInterval property to any number that is valid for the values in the field you're grouping on. For example, if you set GroupOn to Month, valid values for GroupInterval are 1, 2, 3, 4, or 6.

  9. Specify the default sort order for the other group levels on the page.

Create a control that displays the field value as an interval

In some cases, you'll want to create a control that displays the value in the field you grouped on as an interval on the page in Page view or in Microsoft Internet Explorer.

  1. Add a bound HTML control for the expression by clicking Bound HTML Bound HTML control in the toolbox and then clicking in the group header where the control you grouped on an interval is located.

  2. Make sure the bound HTML control is selected, and then click Properties on the toolbar to display the property sheet.

  3. Type the alias for the expression followed by a colon and the expression you want in the ControlSource property box. For example, if you grouped on ShippedDate, and set the GroupOn property to Month and the GroupInterval property to 1, type the following expression to display the values in the GroupOfShippedDate field as a three-letter month:

    Month: Format(GroupOfShippedDate, "mmm")

Note   When you group on a control, Access creates an alias for the control by adding the prefix "GroupOf" to the control's name in the ControlSource property; for example, GroupOfShippedDate: ShippedDate. When you reference this control in an expression or in a property, you must use the alias.

Hide the control you grouped on

In some cases, you won't want the value in the control you grouped on to be visible on the page in Page view or in Microsoft Internet Explorer.

  1. Make sure the control you grouped by date or time interval is selected, and then click Properties on the toolbar to display the property sheet.

  2. Set the control's Visibility property to Hidden.