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

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.

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.

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.

No credit card required