Sorting Numbers in a text field without changing field type

[ychousa]

I was wondering if there's any way to sort numbers in order in a text table without changing field's data type to Number. I need to have numbers(1st, 2nd,...,50th) and some text options(eg.Returned) together in a field, but I can't sort the numbers in order. Thanks for your reply in advance.

 

[ychousa]

I made number part to 1,2,...50 to make things easier. Thanks.

 

[MichaelRed]

If the complete set is properly 'formatted',

? Val("50th")
 50 
? Val("1st")
 1

however the premise is -at best- a hope





MichaelRed

 

[sillysod]

could you store the field as a number and add the st th and nd on the form / report?

 

[BNPMike]

In the query you use to access the data, create another field which is a numeric copy of the field you are interested in. Sort on this field in your application. You don't need to display it.

 

[mordja]

If you sort on CInt(Left([YourField],Len([Your])-2)) you can keep the 1st,2nd,3rd etc format. Just hide the above field if you dont want to display it.

Mordja