Sorting strings

[ryetee]

I have a query that is the data for a report.
I want to sort the data on a string but the string contains a mixture of character and number strings. I want to get the number strings to come out first in alphabetical order but I'm getting the numbers out first unless I sort in descending order in which case the character strings are in reverse order.
ef if I have
Dog
6575631-1R
454646455-2r
Cat
23234234
6546756
Zebra

I get the following (or in reverse if descending)

23234234
454646455-2r
6546756
6575631-1R
Cat
Dog
Zebra

I want
Cat
Dog
Zebra
23234234
454646455-2r
6546756
6575631-1R

Any suggestions

 

[pbaldy]

It feels like a kludge, but one way is a calculated field:

IIf(IsNumeric(Left(FieldName, 1)), 2, 1)

and put the primary sort on that.

 

[plog]

You should make another calculated field and use that one to sort on. It would check the first character to see if its a number, if not you would add an underscore to the front of it ("ABC-">"_ABC"). Sort on that field ascending to accomplish what you want.

 

[ryetee]

It feels like a kludge, but one way is a calculated field:

IIf(IsNumeric(Left(FieldName, 1)), 2, 1)

and put the primary sort on that.

That's what I was trying to remember, thanks.

 

[ryetee]

You should make another calculated field and use that one to sort on. It would check the first character to see if its a number, if not you would add an underscore to the front of it ("ABC-">"_ABC"). Sort on that field ascending to accomplish what you want.

Thanks Plog!

 

[pbaldy]

No problem.