Sorting on mixed text-numeric

[wizard_chef]

In an Access query I am trying to sort on a text field containing
strings like mx1, mx2, ... mx9, mx10, mx11, ...
What I get is a sort like: mx1, mx10, mx11, ... etc. It seems I need
to convert these to numeric but I have tried cInt and a number of
other VBscript conversion functions, and I always get a type-conflict
error.

How can I convert this field in the query so that it will sort
treating the numeric part of the string as numeric rather than
alphabetic?? i.e., mx1, mx2, mx3, ... mx9, mx10, mx11, ... and so on.

 

[Spurious Response]

In an Access query I am trying to sort on a text field containing
strings like mx1, mx2, ... mx9, mx10, mx11, ...
What I get is a sort like: mx1, mx10, mx11, ... etc. It seems I need
to convert these to numeric but I have tried cInt and a number of
other VBscript conversion functions, and I always get a type-conflict
error.

How can I convert this field in the query so that it will sort
treating the numeric part of the string as numeric rather than
alphabetic?? i.e., mx1, mx2, mx3, ... mx9, mx10, mx11, ... and so on.

One thing you could do is change the data so that mx1 becomes mx01.
That way your 1 through 9 data stays properly placed.


Leading zeros on the source data is one solution. Otherwise you get a
sort that leans toward binary

 

[John W. Vinson]

In an Access query I am trying to sort on a text field containing
strings like mx1, mx2, ... mx9, mx10, mx11, ...
What I get is a sort like: mx1, mx10, mx11, ... etc. It seems I need
to convert these to numeric but I have tried cInt and a number of
other VBscript conversion functions, and I always get a type-conflict
error.

How can I convert this field in the query so that it will sort
treating the numeric part of the string as numeric rather than
alphabetic?? i.e., mx1, mx2, mx3, ... mx9, mx10, mx11, ... and so on.

If the alphabetic part is always two characters, you can use a calculated
field

Sortkey: Val(Mid([textfield], 3))

to extract a Number datatype value which will sort correctly.

If the number can occur in variable places in the text string... you may want
to consider redesigning your table. Fields should be "atomic" with only one
value; it seems this field has a text portion and a number portion, which
ideally should be in two fields (which can be concatenated at will for data
display).

John W. Vinson [MVP]