sorting numbers +text

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

Guest

ORDER BY[Championship Competitors].Class,IsNumeric([Comp No])DESC,Val([Comp
No]),[Championship Competitors].[Comp No];

The above gives me a result of :-
class Competiton number

cadet Gp
cadet s10 ............wrong order
cadet s2
cadet 1
cadet 2
cadet 3

any help on how to get s10,s11..... in the right order, any help is much
appreciated.
 
Val() won't help as it looks for numerical characters at the beginning of the
string. For the most par, it stops are the first alphabetical character.
Val("10s") will return 10 but Val("s10") will return 0.

If you want the right sort order, you need to either pad GPs such as "s2"
like "s 2" OR store just the numerical part in another field.

Another thought: If the leading character is always "s", this would work:

Val(Replace([Comp No],"s",""))
 
Thanks Jerry will give it a try.

Jerry Whittle said:
Val() won't help as it looks for numerical characters at the beginning of the
string. For the most par, it stops are the first alphabetical character.
Val("10s") will return 10 but Val("s10") will return 0.

If you want the right sort order, you need to either pad GPs such as "s2"
like "s 2" OR store just the numerical part in another field.

Another thought: If the leading character is always "s", this would work:

Val(Replace([Comp No],"s",""))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

stumpy said:
ORDER BY[Championship Competitors].Class,IsNumeric([Comp No])DESC,Val([Comp
No]),[Championship Competitors].[Comp No];

The above gives me a result of :-
class Competiton number

cadet Gp
cadet s10 ............wrong order
cadet s2
cadet 1
cadet 2
cadet 3

any help on how to get s10,s11..... in the right order, any help is much
appreciated.
Click to expand...
Click to expand...
 
Back
Top