Sort text feild by numbers, numbers and letters, then letters (1 Viewer)

NewbieX

New member
Local time
Yesterday, 18:36
Joined
Apr 11, 2016
Messages
7
I have data that has numbers, numbers and letters and letters and numbers in a text field.
Looks like this
1,1a, 13a, 14a, 15o, PP23, PP23a, 2,44, AB3

Needs to sort like this:
1, 1a, 2, 13a, 14a, 15o, 44, AB3, PP23, PP23a

I used the val(fieldname) function in an Access 2010 query field and it sorts the numbers and numbers/letters OK, but it puts the letters/numbers first (because the value is 0) when I want them last.

Ideas
 

Ranman256

Well-known member
Local time
Yesterday, 21:36
Joined
Apr 9, 2015
Messages
4,339
Unfortunately, text won't sort the same, so, you need to add a numeric field to split the text #s into to sort them numerically.
Give alpha only values a big nimbler,99999. ( not numeric([field])
 

NewbieX

New member
Local time
Yesterday, 18:36
Joined
Apr 11, 2016
Messages
7
Actually I got it to work.

IIf((Val([FieldName) = 0),(Val([FieldName])+ 9999), Val([FieldName] ))

Sorted ascending.
 

Users who are viewing this thread

Top Bottom