Sort query by a column/field with number as string value

[jazfunk]

The attached text file gives an example of the sorting problem I'm encountering.



I'm sorting on a string value column:

01-01
01-02
02-01
02-02
03-01
03-02
etc.

(The first set of numbers represent page numbers in a set of plan sheets. The second set of numbers represent items on each page. The number of pages and items can vary for each project.)

Everything is fine until you get to 3 digit prefix/page numbers (i.e 100-01, etc.). In the text file are 400+ lines of values like the above example, sorted Ascending. The result is not what is desired.

This has been difficult, at best, to put into words. I'm hoping once you see the list you'll see what I want it to do... sort by the numbers left of the "-" (Page#) and within each of those sort by the the numbers to the right of the "-" (Items on a page).

Can anyone point me in the right direction for sorting these Qry's?

[ssanfu]

Create a calculated field in your query by prep-ending a couple of zeros to the field, then take the right 6 characters. The query would look something like:

SELECT Right("00" & [YourField],6) AS SortedNo
FROM YourTable
ORDER BY Right("00" & [YourField],6);

Change the blue to your field and table names

[jazfunk]

That worked brilliantly. Very simple. Thank you.

[ssanfu]

You're welcome