Sorting in Proper Numerical Order, not 1, 10, 11, 12, 2, 20, 21, etc.?

[warrenpage]

Hello everyone. I need help sorting a query and/or a report. I’m using Access 2013.



I have a movie collection database, and I want to create a query and a report for two of its fields. The fields are called DVDColor and DVDNumber.

The DVDColor field has records such as Blue, Green, Red, White, and Yellow.

The DVDNumber field is self-explanatory, going from 1 to 50 or more.

I created a query with DVDColor as the first field, and DVDNumber as the second field.

I ran the query, then sorted it, selecting DVDColor as the first field, and DVDNumber as the second field. So far, pretty routine.
The problem is minor but annoying: The sort created a list that was not in actual numerical order. Here’s what I see:

Blue 1
Blue 10
Blue 11
Blue 12
Blue 2
Blue 20
Blue 21
Green 1
Green 10
Green 11

And so on.

I did the same thing with a report, and the results were the same.

Is there a way to sort a list such as this so the numbers are in actual numerical order? If so, what’s the simplest procedure? Would it be simpler for me to replace the single-digit numbers such as 1, 2, 3, with 01, 02, 03? If that’s the easiest solution, how can I do that without it affecting numbers with two or three digits? Thank you! Warren Page

[orange]

You have sorted by a text field, not a number.
You could try -- in a query--- (untested)

SELECT
DVDColor
,DVDNumbver
FROM
"THeDVD TABLEName goes here'
Order BY
DVDColor
, Int(DVDNumber);

[warrenpage]

Thank you for replying.

I tried your SQL code (removed the typo from DVDNumbver) and inserted the dvd table name but the code did not work.

Is there a way to convert the numerical text to actual numbers? Can I redesign the field while there is already data in it?

I also need to remove the NULL values. I know how to do that: Add WHERE NOT DVDColor is Null. But I'm not sure where to put it in your code.

Lastly, I see commas at the beginning of the third and final lines in your code. I just want to double check and make sure that is correct.

Here is the code I tried, which did not work:

SELECT
DVDColor
,DVDNumber
WHERE NOT DVDColor is Null
FROM
tblMovies
Order BY
DVDColor
, Int(DVDNumber)

Notice I left off the semi colon at the end.
What in this code needs to be changed?

[orange]

You would have to change the datatype of that text field in the Table (to Number or Integer).
The method I showed should only change it for sorting.

The commas just separate values.

[warrenpage]

"You would have to change the datatype of that text field in the Table (to Number or Integer)."

Can that be done with data already entered in the table?

And what is wrong with the SQL code?

[orange]

Where clause in wrong position

SELECT
DVDColor
,DVDNumber

FROM
tblMovies

WHERE NOT DVDColor is Null
Order BY
DVDColor
, Int(DVDNumber)


Since this is only a read query and nothing will change --try it and see if it's what you need.
As I said it is untested.

[warrenpage]

Ahh okay. Thank you. I went ahead and changed the numbers from short text to numbers and the sort worked correctly. I can always change the format back if it causes problems in the future. I tried it on a backup so that the main db won't be affected.

The sort also works well when I filter the form. Thanks for your help. WP