Odd Sorting Requirement

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

Guest

In general when sorting data in a query, records starting 0-9 come before
records with A-Z.

I need the opposite, I want the A-Z records to list first followed by the
0-9 records.

Does anyone have a suggestion for how to accomplish this?

Thanks in advance for any insight you can provide.

Herb
 
Dear Herb:

You could switch to IBM EBCDIC. Not seriously.

You could write a function that converts the first few characters to a
numeric value (perhaps sufficient characters to be unique if you want
perfect sorting) and sort by this. If the characters are always A-Z or 0-9
then there are only 36 possibilities. The function would assign a numeric
value to each. You could do this by doing INSTR() of the string
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ". Add this value into your
accumulation. Before each iteration, multiply the accumulation by 36. You
should be able to do at least 10 characters within a long integer, probably
a bit more.

This value will sort the way you want. If you have more characters than
these 36, don't just change the string by adding new one. You must also
change the multiplier. A larger multiplier may decrease the number of
characters you can represent in this integer.

Does this help at all? Could I give you any further assistance?

Tom Ellison
 
I guess you could crete a (hidden) Calculated Field:

FirstCharIsADigit: IsNumeric( Left([YourField], 1) )

Set ordering DESC on this Calculated Field and THEN your alphabetical
ordering.
 
Back
Top