Re: Sorting on mixed text-numeric

On Sat, 30 Jun 2007 12:21:45 -0700, wizard_chef <wizard_chef@xxxxxxxxxxx>
wrote:

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
.

Relevant Pages

  • Re: Help Needed With Format Of Query Expression
    ... they will BE text and sort as text. ... the CInt or Val function, or you can calculate them instead of using the ...  Microsoft Office Access MVP ...
    (comp.databases.ms-access)
  • Sorting on mixed text-numeric
    ... In an Access query I am trying to sort on a text field containing ... to convert these to numeric but I have tried cInt and a number of ... treating the numeric part of the string as numeric rather than ...
    (microsoft.public.access.gettingstarted)