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

Answer 1

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)

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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)

Answer 8

Perfect! Thanks so much for your help . . .

Answer 9

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.

Answer 10

Thanks so much for your reply . . . I will try that function as soon as I'm able . . . appreciate your time.

Answer 11

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.