O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Sorting Numbers That Are Stored as Text

Problem

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?

Solution

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.

Numbers in a text format sort as text

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.

Sorting text-based numbers as valid numbers

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.

Discussion

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required