Tom said:
If there is a solution that deals with most of the possibilities, let's go
with that. Hopefully, most of the possibilities will include my part
numbers.
Here's my latest try. It sure doesn't agree with the
"sorted" list you posted, but I seems logically consistent
to me.
Give it a try and post back with your comments about where I
went wrong. I expect you to say things like: the dots
should all be before the dashes regardless of the preceeding
number; or the 1PG entry comes after the 11A1 entry because
....
I don't know what your real objective with all this, but as
Chuck said earlier, if people are going to look things up in
the list, it only needs to be sorted in a way that makes
sense to those people, whether its the way they've always
done it or some other way. Actually, unless people don't
know what they are looking for, Chuck's idea of using a
search mechanism (e.g. combo box) is worth exploring
further.
--
Marsh
MVP [MS Access]
Watch out for line wrapping
------------------------------------------------------------------------------------
Public Function StandardizePartNum(Partnum)
Dim k As Integer, j As Integer, n As Integer
Dim DashDot As Integer
Dim LeftPortion As String
Dim MidPortion As String
Dim RightPortion As String
If IsNull(Partnum) Then
StandardizePartNum = Null
Exit Function
End If
' Does the part no. contail a - or .
DashDot = InStr(Partnum, "-")
If DashDot = 0 Then DashDot = InStr(Partnum, ".")
If DashDot > 0 And DashDot <= 4 Then
' There is a dash or dot
LeftPortion = Left(Partnum, DashDot)
For k = DashDot + 2 To Len(Partnum)
If Not Mid(Partnum, k, 1) Like "#" Then Exit For
Next k
MidPortion = Mid(Partnum, DashDot + 1, k - DashDot - 1)
RightPortion = Right(Partnum, Len(Partnum) -
Len(LeftPortion) - Len(MidPortion))
Else
' Does partnum start with a digit?
If Left(Partnum, 1) Like "#" Then
'find end of digits
For k = 2 To Len(Partnum)
If Not Mid(Partnum, k, 1) Like "#" Then Exit For
Next k
If k > Len(Partnum) Then
LeftPortion = ""
MidPortion = Partnum
RightPortion = ""
Else
'find end of non-digit
For j = k + 1 To Len(Partnum)
If Mid(Partnum, j, 1) Like "#" Then Exit For
Next j
If j > Len(Partnum) Then
LeftPortion = ""
MidPortion = Left(Partnum, k - 1)
RightPortion = Mid(Partnum, k)
Else
For n = j + 1 To Len(Partnum)
If Not Mid(Partnum, n, 1) Like "#" Then
Exit For
Next n
LeftPortion = Left(Partnum, j - 1)
MidPortion = Mid(Partnum, j, n - j)
RightPortion = Mid(Partnum, n)
End If
End If
Else 'partnum starts with non-digit
'find end of non-digits
For k = 2 To Len(Partnum)
If Mid(Partnum, k, 1) Like "#" Then Exit For
Next k
If k > Len(Partnum) Then
LeftPortion = Partnum ' this should not
happen?
MidPortion = ""
RightPortion = ""
Else
'find end of digits
For j = k + 1 To Len(Partnum)
If Not Mid(Partnum, j, 1) Like "#" Then Exit
For
Next j
If j > Len(Partnum) Then
LeftPortion = Left(Partnum, k - 1)
MidPortion = Mid(Partnum, k)
RightPortion = ""
Else
LeftPortion = Left(Partnum, k - 1)
MidPortion = Mid(Partnum, k, j - k)
RightPortion = Mid(Partnum, j)
End If
End If
End If
End If
StandardizePartNum = _
LeftPortion & Space(6 - Len(LeftPortion)) _
& Right("0000000" & MidPortion, 7) _
& RightPortion
End Function
