On Tue, 25 Sep 2007 23:49:43 -0500, Marshall Barton
wrote:

Tom wrote:
[snip a whole lot[
I do not have to deal with any part numbers with these characters. I only
have to deal with dots and dashes.

I have tried to show the proper sorting of the numbers based on the left,
middle and right parts of the number.

A 1.?? comes before a 11.?? and before a 111.??
A 1.1 comes before a 1.11 and comes before a 1.111, etc.


Just to let you know that I have not forgotten you and I am
still trying to figure something out as time permits.

I think I may have part of it, but even though I have a fair
amount of experience with weird sorts, this one is a pretty
tough nut to crack. In particular, these troublesome ones:

11-11A12,##a,##,a##
111-D1,###a,a#,s
11A11A,##a,##,a
1PG,s,#,aa
SNA6A24D,aaa,#,a##a
TSSRD200A,aaaaa,###,a

seem to defy any logic I have come up with so far. What
works for some of them won't work for others

Marshall,

Are you treating everything after the first coma as data for additional fields?
If so the problem is a little more manageable.

Would you please check UDF below to see if it does the job.
I'm so thoroughly confused now that I'm going to have to let it rest for a few
days before looking at it again.
You will note that the table field name used is 'PartNum'

Just a wizard prodder (doesn't mean I 'can't' write code, means I don't if it
is possibly avoidable)
Chuck
--

Public Function StandardizePartNum3(PartNum)
Dim k As Integer, j As Integer, m As Integer

If IsNull(PartNum) Then
StandardizePartNum3 = Null
Exit Function
End If

'find first Dot
For k = 1 To Len(PartNum)
If Mid(PartNum, k, 1) Like "." Then Exit For
Next k

'find first Dash
For j = 1 To Len(PartNum)
If Mid(PartNum, j, 1) Like "-" Then Exit For
Next j

'find first alpha character
For m = 1 To Len(PartNum)
If Not Mid(PartNum, m, 1) Like "#" Then Exit For
Next m

If m = 1 Then
StandardizePartNum3 = PartNum
Exit Function
End If

If (m 1 And k Len(PartNum)) Then
StandardizePartNum3 = "000" & PartNum
Exit Function
End If

If (m 1 And j Len(PartNum)) Then
StandardizePartNum3 = "00" & PartNum
Exit Function
End If

If (m 1 And k Len(PartNum) And j Len(PartNum)) Then
StandardizePartNum3 = "0" & PartNum
Exit Function
End If

End Function