850 Chapter 28 Display Data
- Experiment with various ways of sorting the records to display different results.
Then close the Customers table, clicking No when prompted to save the table
layout.
CLEAN UP Retain the GardenCompany04 database for use in later exercises.
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 telephone numbers, the data type of these
fields is usually 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 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 numbers are
defined as text.