Sort records on numeric values stored in a text field

Numbers and strings stored in a text or memo field are sorted alphabetically. In other words, numbers will be sorted based on the individual digits that make up the value, instead of on the numeric value. For example, the value 11 appears before 2, and the value 12 appears before 3, as shown in the following table.

Student Name	Student No
John	1
Maria	10
William	11
Mary	12
Jane	2
Lynn	3
Jason	4
Mary	5

To ensure that the numbers are sorted based on their numeric value instead of alphabetically, do one of the following:

• If the field contains only numeric values, consider changing the data type of the field to Number or Currency.

• Use the following procedure if:

  • The field contains only numeric values, but you do not want to change the data type of the field.

  • The field contains both numeric and string values, but you want ignore the string values when sorting.

This procedure uses the Val function to obtain the numeric value of a number that is stored as text. The expression also uses the IIf function to handle null values that might be in the sort field.

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

2. Type the expression Expr1: IIf([Fieldname] Is Null, 0, Val([Fieldname])) in the Field row in the first column.

   The IIf function checks to see if the field value is null and, if so, treats it as 0. If the value is not null, the IIf function calls the Val function to obtain the numeric equivalent.

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

   An ascending sort displays the record with the smallest value on top and the record with the largest value at the bottom. A descending sort does the opposite.

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

If the preceding procedure does not work for you, there is another approach you can try that avoids altering the structure of the table. This involves "padding" values that contain fewer digits with leading zeroes. For example, you change the values 11, 2, 2000, and 3 to 0011, 0002, 2000, and 0003 before you apply the sort.

Top of Page