Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Sort in Numerical Order in a Query

    Hello,

    How can I sort numerical numbers in an Access query? I sort in ascending order, but it will not sort correctly. For example, I sort the following numbers and here is what I receive:

    I receive the following when sorting in ascending order:
    1
    2
    20
    3
    4

    I would like to receive the numbers in the following order:
    1
    2
    3
    4
    20

    I am baffled on this one. :-)

    Thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Check the Data Type for your numbers field it maybe TEXT and not number.
    Alternatively you could add a column in your query and add the following in the field of the new column sort_order: Format([test_1],"0000") where test_1 is the name of your field to be sorted. The Format([test_1],"0000") pads the number to 4 digits e.g. 1 becomes 0001, 2 becomes 0002 and 20 becomes 0020. Remove the sort on the current field that has the number and sort the new field in ascending order.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If adding a new column...
    You can also format the field as "#0" which will show 1, 2, 3, 35, 50, 255, 500 intead of 0001, 0002, 0003, 0035, 0050, 0255, 0500, etc..

    I'm not sure but you may also be able to use int([Field]) or abs([Field]). Again, I'm not sure. I think there is also something like Cint([Field]) which converts it to an integer but again, look this up in the help. If you do it this way, your best bet is to try the Format first as Poppa Smurf suggested.

    Ideally though, you'd want to change the data type of the field to number if this doesn't cause any other problems.
    Last edited by pkstormy; 11-07-08 at 02:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If these characters are numeric and nothing but numeric then agreed - change the column type to number. There's lots of reasons for this, all regarding good, solid database design.

    If there is some text in there (for example if this is a questionnaire numbers, like 80a, 80b) then you have a new problem. The datatype is appropriate and you can't convert the values to numbers. We can get round that but you will need to suss out the nature of your data first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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