# Thread: Sort in Numerical Order in a Query

1. Registered User
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. Registered User
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. Moderator
Join Date
Dec 2004
Location
Posts
3,928
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 01:26.

4. King of Understatement
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.

#### Posting Permissions

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