Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting a text field to a numeric field (2000)

    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

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting a text field to a numeric field (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •