How do I sort numbers & text in an Access text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data in a field e.g.
1
3
0
5
24
36789
L345
MS234
etc....
What I need is some way of sorting the so the numbers come first and in
numeric order then the letter number combinations.
 
I might be being thick here but this does not appear to sort them as expected.
The Val part does put the numbers in the correct order but the alpha
numerics get a value of 0 so come first in the query. If I then try to do
the second order you are back to square one with 1,10,100 etc as the numeric
sort, and the alpha numerics are not in order eg B80 comes before B8


Alex Dybenko said:
Hi,
you can first order by Val([MyField]) and then by [MyField]

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


avro45 said:
I have data in a field e.g.
1
3
0
5
24
36789
L345
MS234
etc....
What I need is some way of sorting the so the numbers come first and in
numeric order then the letter number combinations.
Click to expand...
Click to expand...
 
Try

ORDER BY IsNumeric(MyField) Desc, Val(MyField), MyField

IsNumeric should return -1 (true) and 0 False.
Val will return the value and will still return zero for the non-numbers, but
they are forced to the second section by the order by criteria.


I might be being thick here but this does not appear to sort them as expected.
The Val part does put the numbers in the correct order but the alpha
numerics get a value of 0 so come first in the query. If I then try to do
the second order you are back to square one with 1,10,100 etc as the numeric
sort, and the alpha numerics are not in order eg B80 comes before B8

Alex Dybenko said:
Hi,
you can first order by Val([MyField]) and then by [MyField]

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


avro45 said:
I have data in a field e.g.
1
3
0
5
24
36789
L345
MS234
etc....
What I need is some way of sorting the so the numbers come first and in
numeric order then the letter number combinations.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top