Sort records based on partial values in a field

There may be times when you want to sort records in Access based only on the month portion of date values and ignore the day and year. Or, you might want to sort records based only on the first few characters of a text field. This topic shows you how to do perform these searches by creating simple expressions.

What do you want to do?

• Sort on partial date values

• Sort on partial text values

Sort on partial date values

When you sort records by using one of the Sort commands, the entire date value — day, month, and year — is used to determine the position of each record. But suppose you want to sort birthdays based only on the months in which they occur, and ignore the day and year portion of the values.

Sorting on just a portion of the date values requires writing an expression. You can sort on the data in a table or the results of a query — but you must first be in Datasheet view. You write your expression in the query region of the Filter tab. The expression you create extracts just the data you want and then uses it as the basis for sorting.

Display the Filter tab

1. Switch your table or query to Datasheet view.

   If you are working with a query, run the query to display the results in Datasheet view.

2. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

   Access displays a new object tab with Filter in the name.

Create the expression

1. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

2. Type an expression in the Field row in the first column. For example, to sort by month regardless of the year, type the expression Expr1: DatePart("m",[BirthDate]) in the Field row in the first column.

3. To sort records by days within each month, type Expr2: DatePart("d",[BirthDate]) in the Field row in the second column.

   The DatePart function in the first column sorts the records by month, and the DatePart function in the second column sorts the records by days within each month.

4. In the Sort row, select Ascending or Descending for each of the expressions.

   An ascending sort in the first column displays January at the top and December at the bottom. An ascending sort in the second column displays 1 at the top and 31 at the bottom. A descending sort order does the opposite.

5. On the Home tab, in the Sort & Filter group, click Toggle Filter.

Top of Page

Sort on partial text values

Sorting on partial text values is very similar to sorting on partial date values. You need to write an expression in the Advanced Filter/Sort window to extract the characters that form the basis for sorting.

Assume that the City field contains values such as North Seattle, South Seattle, North Tacoma, or South Tacoma. Suppose you want to ignore the first word and sort the records on the second word alone.

1. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

2. Type an expression in the Field row in the first column. For example, type =Mid([City], 7) to ignore the first six characters in the City field.

   The Mid function returns a substring contained in a specified string or field. In this case, for each record, the function returns the value starting from the seventh character (that is, after the space) in the City field.

3. In the Sort cell, select Ascending or Descending.

4. On the Home tab, in the Sort & Filter group, click Toggle Filter.

Top of Page