Hi!

Try:
iif(left(cField,1)$"0123456789", " "+padl(val(cField),4,"0"), left(cField,1)+padl(val(substr(cField,2)),4,"0")) + cField

You do not need to use STR() before using PADL(). On above expression chekcing if first letter is number. If yse - use a number, if not - use a number after first letter, add first letter before (for correct sorting). For correct sorting of all all values that have the same [letter]+number part, add the value to the end (for example, "B2B and "B2C" will be converted to "B0002 B2B" and "B0002 B2C" - second is greater, so sorting is ok.)

See if above expression is good anough for indexing. If not, try to replace "0123456789" by a constant.


>hi all,
>
>I have a table with a char type key field, the key can be in any combination of letters and interger.. following is a sorted example:
>

>1, 1A, 2, 2A, 2B, 10, 10A, B2, B10B, C10, C11A, C12B, D...
>

>
>I have to use the INDEX ON command to set them in a correct order..
>
>my command is:
>

>index on iif(val(keyField)=0, ;
>   keyField, ;
>   padl(alltrim(str(val(keyField), 10, 0)),4,"0")+substr(keyField,len(allt(str(val(room_num), 10, 0)))+1)) ;
>   to TEMP_DIR+"\MYINDEX.CDX"
>

>
>with this line B2 would be larger than B10, but the INDEX ON command does not allow me to write a longer statement, anyway for my user B10 is very rare so it's ok.
>
>My real problem is, if the table already have 2A in it, and I append a record with keyField="2", 2 will be arranged after 2A.
>
>But when I check my code I should have converted 2 to 0002 and 2A to 0002A, any idea please? Any work around or whatsoever is ok, I just want my table to have a correct order.
>
>Thanks in advance!
>Jimi