This topic provides reference information about:
Data types required to group records on prefix characters
How a page that groups records on the first characters in a field looks in Design view
Settings for properties in the Sorting And Grouping box
When you group records on the first n (or prefix) characters in a text field, the control you group on must be bound to a field of one with the following data types: a Text data type in a Microsoft Access database (.mdb); or a char, varchar, nchar, or nvarchar data type in a Microsoft Access project (.adp).
Return to top
To group records on the first n characters, you need two controls bound to the field you want to group on. You use one control to group the records by the specified number of prefix characters, and then display the prefix characters. You use the other control, which is in the next lower group level, to sort records and display the actual value of the field.
In the following example, the records come from the Products table. In the first group level, the records are grouped on the first character of the values in the ProductName field. In the next lower group level, the page displays and sorts the records by the actual values in the ProductName field.
Return to top
The following illustration shows the layout of the example page in Design view.
Return to top
The following table lists the properties that are set for the example page in the Sorting And Grouping box. When you don't specify ASC or DESC after the field name in the DefaultSort property, Microsoft Access sorts the records in ascending order.
| Property | Group Record Source Products-ProductName |
Group Record Source Products |
|---|---|---|
| CaptionSection | No | Yes |
| GroupHeader | Yes | Yes |
| GroupFooter | No | No |
| RecordNavigation Section | Yes | Yes |
| ExpandedByDefault | No | No |
| DataPageSize | 3 | 3 |
| GroupOn | Prefix Characters | Each Value |
| GroupInterval | 1.0 | 1.0 |
| DefaultSort | GroupOfProductName | ProductName |
Note When you group on a field, Access creates an alias for the control by adding the prefix "GroupOf" to the control's name in the ControlSource property; for example, GroupOfProductName: ProductName. When you reference this control in an expression or property, you must use the alias.
Return to top
Return to Group records by the first n characters in a text value on a data access page