Sorting Report entries on alphanumeric combination

Question

I am trying to move over a database that was previously stored in an excel workbook. The previous setup had a different worksheet for each bookshelf and the individual shelves were listed at "A-levels" such as A1, A2, A32, etc... However, on Some shelves, there are subsets such as A9A1, A9A2, A9A5, etc...

The report format that I have created has each different bookshelf on a new page when printed, but based on the sorting, when there are multiple entries for the same shelf (A11A1, A11A4, etc...) it falls apart and they don't stay in order.

Is there any sort expression that you could use to first sort by number, then IF there is another letter, sort by letter, and then sort that subset by their respective numbers?

Answer 1

Your tag says MS Access, if you are using Jet/ACE in MS Access, you can sort by a user defined function.

Very roughly

SQL

SELECT t.ID, t.SortHere, SortOrder([SortHere]) AS ToSort
FROM Table t
ORDER BY SortOrder([SortHere]);

Code

Function SortOrder(ToSort)

Part1 = Left(ToSort, 1)

For i = 2 To Len(ToSort)
    If Not IsNumeric(Mid(ToSort, i, 1)) Then
        Part2 = Part2 & Mid(ToSort, 2, i - 2)
        Part3 = Mid(ToSort, i, 1)
        Part4 = Mid(ToSort, i + 1)
        Exit For
    End If
Next

If Part2 = vbNullString Then
    Part2 = Mid(ToSort, 2)
End If

SortOrder = Part1 & Format(Part2, "0000") _
          & Part3 & Format(Part4, "0000")

End Function

I am sure you can come up with a better function with a little thought.