Converting a text field to a numeric field (2000)

[kinaida]

Is it possible to convert a text field to a numeric field within a query? I want to sort on a field within my query that is set as text but stores digits (sort 1,2,3,4,5...), and I can't modify the tables. The query is currently sorting (1,10,11,12,13....2,20,21,22....)

Thanks!
Kindra

[Alexya1]

Hi Kindra...

Try creating a calculated field in the query and sorting on that...
For example... fldNumeric: CLng([fldText])
This will Convert the Text Values to Long Integer... (There are numerous conversion functions... CDbl (convert to double), CStr(convert to string), etc... )
If you then set the sorting to ascending on fldNumeric field, your resulting records will be sorted correctly...
You can also hide the calculated field so that there is no confusion between the two...

HTH