Database Reference
In-Depth Information
How Access sorts
The concept of sorting seems quite intuitive, but sometimes the way Access sorts
numbers might seem puzzling. In Access, numbers can be treated as either text or
numerals. Because of the spaces, hyphens, and punctuation typically used in street
addresses, postal codes, and phone numbers, the data type of these fields is usually
Short Text, and the numbers are sorted the same way as all other text. In contrast,
numbers in a field assigned the Number or Currency data type are sorted as numerals.
When Access sorts text, it sorts first on the first character in the selected field in every
record, then on the next character, then on the next, and so on—until it runs out of
characters. When Access sorts numbers, it treats the contents of each field as a single
value, and sorts the records based on that value. This tactic can result in seemingly
strange sort orders. For example, sorting the list in the first column of the following
table as text produces the list in the second column. Sorting the same list as numerals
produces the list in the third column.
Original
Sort as text
Sort as numerals
1
1
1
1234
11
3
23
12
4
3
1234
11
11
22
12
22
23
22
12
3
23
4
4
1234
If a field with the Short Text data type contains numbers, you can sort the field
numerically by padding the numbers with leading zeros so that all entries are the
same length. For example, 001, 011, and 101 are sorted correctly even if the num-
b ers are defined as text.
