Quick Order By Question 1

[FoxFool]

This is probably a dumb question, but I have a field that contains keycodes of A,B,C,D, etc. Anything over Z gets AB,AC,AD. My question is how to use set order to correctly so that the order is A,B,C,D, and not A,AB,AC,AD,B,C,D like I am getting now. I have exact set ON. Thanks a lot in advance.

 

[papadopo]

here is a solution by introducing a new field.

Add a field MyOrder I(4) in your table.

Run the following:

j = the max number of characters in field keycodes

FOR i = 1 TO j

SELECT MyCur

SCAN FOR LEN(ALLTRIM(keycodes))= i

FOR y = 1 TO i

nThisCode = ASC(SUBSTR(ALLTRIM(keycodes),y,1))

ENDFOR

REPLACE MyOrder WITH (i-1)*ASC('Z')+nThisCode


ENDSCAN

ENDFOR

SET ORDER TO MyOrder

----------
"Never Send a Human to do a Machine's Job"

 

[papadopo]

pfff

its getting really late... there seems to be a problem with the code in the solution above.

but i hope you get the idea.

----------
"Never Send a Human to do a Machine's Job"

 

[wgcs]

how to use "set order"

To use "SET ORDER", you must first have an index.
To create an index so that A and B shows before AA, AB, etc, and AAA, AAB, etc show even later, try this:

INDEX ON PADL( ALLTRIM(keycode) ,3) TAG KeyCode
SET ORDER TO KeyCode

since 'space' sorts lower than 'A', the '(space)(space)A'..'(space)(space)Z' will be lower than '(space)AA' and 'AAA'