  asked on

# Display room numbers in numberical order e.g. A1, A10 - rather than A1, A2

Hi this should be relatively easy to sort out
I have room numbers
A1, A2, A3, A4....A20, B1, B2........
But when they are displayed in order in Access they appear like:
A1, A10, A11, A12.......A18, A19, A2, A20, B1, B10.........
I believe this is because they are a text field.
Any Ideas how to solve it
Thanks
Joe
Microsoft Access Last Comment
Patrick Matthews

8/22/2022 - Mon
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
ee_rlee

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Patrick Matthews

Joe,

If you do not know ahead of time how many characters the text portion will have, including 0
characters, then:

1) Add this UDF to a regular module:

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
Optional MatchCase As Boolean = True)

' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
' pattern (PatternStr).  Use Pos to indicate which match you want:
' Pos omitted               : function returns a zero-based array of all matches
' Pos = 0                   : the last match
' Pos = 1                   : the first match
' Pos = 2                   : the second match
' Pos = <positive integer>  : the Nth match
' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
' returns an empty string.  If no match is found, the function returns an empty string

' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).

' If you use this function in Excel, you can use range references for any of the arguments.
' If you use this in Excel and return the full array, make sure to set up the formula as an
' array formula.  If you need the array formula to go down a column, use TRANSPOSE()

Dim RegX As Object
Dim TheMatches As Object
Dim Counter As Long

' Evaluate Pos.  If it is there, it must be numeric and converted to Long
If Not IsMissing(Pos) Then
If Not IsNumeric(Pos) Then
RegExpFind = ""
Exit Function
Else
Pos = CLng(Pos)
End If
End If

' Create instance of RegExp object
Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = PatternStr
.Global = True
.IgnoreCase = Not MatchCase
End With

' Test to see if there are any matches
If RegX.test(LookIn) Then

' Run RegExp to get the matches, which are returned as a zero-based collection
Set TheMatches = RegX.Execute(LookIn)

' If Pos is missing, user wants array of all matches.  Build it and assign it as the
' function's return value
If IsMissing(Pos) Then
ReDim Answer(0 To TheMatches.Count - 1) As String
For Counter = 0 To UBound(Answer)
Next

' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
Else
Select Case Pos
Case 0                          ' Last match
RegExpFind = TheMatches(TheMatches.Count - 1)
Case 1 To TheMatches.Count      ' Nth match
RegExpFind = TheMatches(Pos - 1)
Case Else                       ' Invalid item number
RegExpFind = ""
End Select
End If

' If there are no matches, return empty string
Else
RegExpFind = ""
End If

' Release object variables
Set RegX = Nothing
Set TheMatches = Nothing

End Function

2) Use it in your query like this:

ORDER BY RegExpFind(Room, "^\D+", 1, False), Val(RegExpFind(Room, "\d+", 1)