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?

1 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


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


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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.