Converting a text field to a numeric field (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote