odd sort order

[Mark Kubicki]

i've got a project that requires an odd (completely illogical) sort order
and I need help with the SQL to get it to sort as needed. It might be
easier to explain than to descrip... (let's try):

TA
TB
TC series
TC-1
TC-2
TC-1
TD
....
TX
TY
TZ
TAA
TAB
TAC
TAD
TAE series
TAE-1
TAE-2
....
TAZ
TBA
TBB
TBC
etc...

The space in Tx series could be a space or dash or anything similar.l
Likewise, the dash in Tx-1, Tx-2 does not have top be a dash

many thanks in advance.
mark

 

[Jerry Whittle]

In a query put something like below in a field heading with the proper field
name within the [ ]:

TheSort: IIf(Len([TheField])= 2,1 & [TheField], 2 & [TheField])

Sort on this field. You can remove the checkmark for show.

 

[vanderghast]

Except for the case:

TC-1
TC-2
TC-1 ' <--- ?


and assuming the delimiter is either a space, either an -, I would use:



ORDER BY InStr( Replace(fieldName , "-", " ") & " " , " "),
Replace(fieldName, "-", Chr(255))


The first Replace bring back the cases to the single case of a space, as
delimiter.

The InStr find the position of the first delimiter, and so, all the records
having, say, a space in postion 3, will be 'grouped' together. As usual for
an

ORDER BY f1, f2

in case of equality on f1, then it is f2 which is picked to 'break' the
equalities. Since



TC series
TC-1
TC-2

is the proper order,


? "TAE series" < "TAE-1"
True


it seems nothing else has to be done and so


ORDER BY InStr( Replace(fieldName , "-", " ") & " " , " "),
Replace(fieldName, "-", Chr(255))



is thus all what is required. But I have made many assumptions, which can be
wrong. Unfortunately, "-" is an invisible character under Win32, for
ordering, same as ', so I chose to replace it, to bypass that problem.



Vanderghast, Access MVP