Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting text field of mostly numbers in a form (Access 2000)

    I have a text field in a table that has mostly numbers. I would like to have the numbers sort correctly (numerically) when being recalled by a form. Is there a way to use the OrderBy preferences field to force the numbers into numerical order? I was thinking I could use the Val() function in OrderBy like I did in a query to organize the numbers but it doesn't seem to work.

    Also, I have reasons why I don't want to change my field to a number field, so I was hoping to find a way to order the data without changing the field type. I also want the form to be able to accept data so I can't obtain the data off the query.

    Any suggestions?
    Thanks,
    James

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting text field of mostly numbers in a form (Access 2000)

    Let's say that your field is named TextField.

    Create a query based on the table.
    Add a calculated column IsNumeric([TextField])
    Clear the Show check box and set the sort order to Ascending if you want to sort numbers before text, or to Descending if you want to sort text before numbers.
    Add another calculated column Val([TextField])
    Clear the show check box and set the sort order to Ascending.
    Add the fields from the table, or * if you want to use all fields.
    Use this query as record source for your form.

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting text field of mostly numbers in a form (Access 2000)

    Yes! Thank you, I knew I was missing something that simple.
    It works great.
    James

Posting Permissions

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