# Need to sort Text Field with a mix of numbers, nulls, and text data

• ### Question

• I have a query where I need to sort by the PEN number . . . but there are a mixture of null values, numbers, and text values, because some of the pens are descriptive in nature and not numbered. . .

The field is defined as text . . .when I use the VAL([pen]) function, I get 0 for blank field, numeric for numbers, and -Error for the Text items . . .

Any recommendations for accomplishing this? ? ?  Thanks

Friday, October 2, 2020 6:55 PM

• In a query based on the table, add a calculated column:

SortPEN: IIf(IsNumeric([PEN]),Format([PEN],"000000"),[PEN])

Clear the Show check box for this column, and sort on it instead of on PEN itself.

Regards, Hans Vogelaar (https://www.eileenslounge.com)

• Marked as answer by Friday, October 2, 2020 8:09 PM
Friday, October 2, 2020 7:51 PM
• Need nulls first, then numeric sorting 1,2,3,4,5...10,11,12...,21,22,23, then alpha sorting A,B,C,D,E,F,front pens. . .

I realize Text field definition is causing this, but nothing I can do about the naming conventions of the pens

Hi Big Itch,

You can use a function MySorting. Include it in your SQL-string as:   SORT BY MySorting(PEN)

```Function My_sorting(cur_value) As String
If (IsNull(cur_value)) Then
My_sorting = ""
ElseIf (IsNumeric(cur_value)) Then
My_sorting = Right(String(10, "0") & cur_value, 10)
Else
My_sorting = cur_value
End If
End Function
```

This function converts Null's to a zero-length-string, numerics to a string of 10 characters, padded with "0" on the left side, else the original (text) value. This text value should not start with a space, else you have to trim the value.

Imb.

• Marked as answer by Friday, October 2, 2020 8:12 PM
Friday, October 2, 2020 8:10 PM

### All replies

• If you sort by the PEN number field itself, is the result different from what you expect/want? If so, in what way?

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Friday, October 2, 2020 7:03 PM
• but there are a mixture of null values, numbers, and text values, because some of the pens are descriptive in nature and not numbered. . .

Hi Big Itch,

How do you want to compare the numbers with text values? Numbers treated as text strings will order like:

1 - 11 - 111 - ... - 2 - 20 - 200 - ... - 201 - ... - 21 - ...

thus not at all a "numeric" ordening.

Must the numbers be sorted numeric, and the text values alphanumeric? First the numerics, and later the alphanumerics, or the other way around?

Imb.

Friday, October 2, 2020 7:13 PM
• At the top: 1,10,101,102....,11,110,111,112

further down near the end of numbers: 9,96,97,98,99,A,B,C,D,E,F,front pens . . . .g,h,outside

Friday, October 2, 2020 7:15 PM
• Need nulls first, then numeric sorting 1,2,3,4,5...10,11,12...,21,22,23, then alpha sorting A,B,C,D,E,F,front pens. . .

I realize Text field definition is causing this, but nothing I can do about the naming conventions of the pens

Friday, October 2, 2020 7:17 PM
• At the top: 1,10,101,102....,11,110,111,112

further down near the end of numbers: 9,96,97,98,99,A,B,C,D,E,F,front pens . . . .g,h,outside

Hi Big Itch,

Isn't this just: SORT BY <PEN field>  (the field that holds the PEN  number)?

Imb.

Friday, October 2, 2020 7:19 PM
• When I sort by the field [pen], I get the results I replied with . . .numbers sort like text because it's defined as a text field. . .

Results are:

At the "padding-right:0px;padding-border:none;font-variant-numeric:inherit;font-variant-east-asian:inherit;font-size:14px;line-height:inherit;font-family:'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif;vertical-align:baseline;list-style-type:none;color:#2a2a2a;">further down near the end of numbers: 9,96,97,98,99,A,B,C,D,E,F,front pens . . . .g,h,outside

Friday, October 2, 2020 7:26 PM
• In a query based on the table, add a calculated column:

SortPEN: IIf(IsNumeric([PEN]),Format([PEN],"000000"),[PEN])

Clear the Show check box for this column, and sort on it instead of on PEN itself.

Regards, Hans Vogelaar (https://www.eileenslounge.com)

• Marked as answer by Friday, October 2, 2020 8:09 PM
Friday, October 2, 2020 7:51 PM
• Perfect! Thanks so much for your help . . .
Friday, October 2, 2020 8:09 PM
• Need nulls first, then numeric sorting 1,2,3,4,5...10,11,12...,21,22,23, then alpha sorting A,B,C,D,E,F,front pens. . .

I realize Text field definition is causing this, but nothing I can do about the naming conventions of the pens

Hi Big Itch,

You can use a function MySorting. Include it in your SQL-string as:   SORT BY MySorting(PEN)

```Function My_sorting(cur_value) As String
If (IsNull(cur_value)) Then
My_sorting = ""
ElseIf (IsNumeric(cur_value)) Then
My_sorting = Right(String(10, "0") & cur_value, 10)
Else
My_sorting = cur_value
End If
End Function
```

This function converts Null's to a zero-length-string, numerics to a string of 10 characters, padded with "0" on the left side, else the original (text) value. This text value should not start with a space, else you have to trim the value.

Imb.

• Marked as answer by Friday, October 2, 2020 8:12 PM
Friday, October 2, 2020 8:10 PM
• Thanks so much for your reply . . . I will try that function as soon as I'm able . . . appreciate your time.
Friday, October 2, 2020 8:13 PM
• Thanks so much for your reply . . . I will try that function as soon as I'm able . . . appreciate your time.

Hi Big Itch,

You could even "sort" the Null's at the end of the list, by assigning them a value of e.g. "ZZZ". If necessary you can place some ranges, e.g. "front pens" before or after the other alphanumerical pens, by converting it to the appropriate value.

Just play with it.

Imb.

Friday, October 2, 2020 8:28 PM