Our system stores certain numeric data as text. When it comes to sorting the data, it sorts alphanumerically, which is not appropriate for the reporting we need. How can the text-based numbers be coerced to sort as real numbers?
Figure 5-8 shows how apparently numeric data sorts when it is saved as the Text data type. All the ones come first, so numbers such as 1, 10, 100, 101, and so on will all appear before the number 2 in the list.
Figure 5-8. Numbers in a text format sort as text
To sort this data in the expected numerical way, you must add an extra field that, for each record, holds a converted value of the text-based number. The converted value becomes the real numeric data point. Then, the sort is placed on the new field.
Figure 5-9 shows a query design that includes an expression field that converts the text numbers to integers (with the
CInt function). The sort is placed on this new field.
Figure 5-9. Sorting text-based numbers as valid numbers
Note that it is not necessary to have the expression field appear in the output. Figure 5-10 shows the result of running the query.
An alternative method is to pad the text-based numbers with leading spaces or zeroes. The technique is to find the largest number and determine ...