# For Pat. (1 Viewer)

#### ChrisO

##### Registered User.
For Pat.

Code:
``````[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.

#### Attachments

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.

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.

Last edited:

Replies
8
Views
281
Replies
3
Views
463
Replies
3
Views
218
Replies
7
Views
339
Replies
6
Views
463