Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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