For Pat. (1 Viewer)

[ChrisO]

For Pat.

[color=green]'1
'10
'100
'2
'20
'3 a
'4D-1
'E3-2
'E10-1
'are all possibilities and should end up as
'1
'2
'3 a
'4D-1
'10
'20
'100
'E3-2
'E10-1[/color]

Public Function SortBy(ByVal vntIn As Variant) As Variant
    Dim lngIndex As Long
    Dim strTemp  As String

    Const MaxLen As Long = 100
    Const PadChr As String = "_"

    If Len(vntIn) Then
        For lngIndex = 1 To Len(vntIn)
            If IsNumeric(Mid(vntIn, lngIndex, 1)) Then
                strTemp = strTemp & Mid(vntIn, lngIndex, 1)
            Else
                Exit For
            End If
        Next lngIndex
    
        If Len(strTemp) Then
            SortBy = String(MaxLen - Len(strTemp), PadChr) & strTemp
        Else
            SortBy = String(MaxLen - Len(vntIn), PadChr) & vntIn
        End If
    End If

End Function

Called as:-
SELECT tblSomeTable.DrawingNumber, SortBy([DrawingNumber]) AS SortedBy
FROM tblSomeTable
ORDER BY SortBy([DrawingNumber]);


It complies with the stated requirements but I think it verges on the impossible for all possible combinations.
For example: we would need to know how to handle such entries as E20.

It’s just a start…

Regards,
Chris.

 

[Pat Hartman]

Thanks Chris,
I'll take a look at it. I already told them it was unlikely I could completely solve the problem since they make up new "numbering" schemes all the time. I'm hoping that leading numbers will sort "correctly" and numbers following the same prefix will sort correctly. I told them they were on their own with the suffixes.

 

[ChrisO]

Hi Pat.

The following is the best I can do and so I will leave it at this…

Sort key definition:-
Spaces are removed.
A Null, Empty or ZLS argument will sort to top of list.
Only single or contiguous ‘numbers’ in the argument are right justified else left justified.
Each encounter of a non-contiguous character type in the argument starts a new field in the sort key.

As written, ‘Numbers’ are right justified into a field length of 20 characters maximum, change as required.

The code is written to comply with ‘on page view’ and so is broken up into smaller procedures.

I can not guarantee it fully complies with your user’s requirements but it is closer.

Edit
It might be wise to limit the sort string to 255 characters in
Private Function HandleConcatenation
so:-
HandleConcatenation = Left(strTemp, 255)


Chris.