If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Attn: Marshall Barton
Chuck wrote:
Due to the nature of the part numbers we are working with, parsing them left, center, right did not make any difference in the sort order. Your milage may vary for part numbers with different formats. I came very close a month ago, but there are some part numbers did not fit the simple parsing rules I used. As I see the problem, the trick is to identify which characters are in the left, middle and right portions given the apparently ambiguous mix of alphas, digits, the dot/dash and the fact that each portion can vary from 0 to 6 characters. There's even one example where the middle portion is not all digits. It's enough to give me a headache, but intriguing in a twisted sort of way. -- Marsh MVP [MS Access] |
#22
|
|||
|
|||
Attn: Marshall Barton
On Thu, 27 Sep 2007 12:38:23 -0500, Marshall Barton
wrote: Chuck wrote: Due to the nature of the part numbers we are working with, parsing them left, center, right did not make any difference in the sort order. Your milage may vary for part numbers with different formats. I came very close a month ago, but there are some part numbers did not fit the simple parsing rules I used. As I see the problem, the trick is to identify which characters are in the left, middle and right portions given the apparently ambiguous mix of alphas, digits, the dot/dash and the fact that each portion can vary from 0 to 6 characters. There's even one example where the middle portion is not all digits. It's enough to give me a headache, but intriguing in a twisted sort of way. You can: find first number, find first character, find first number after first character, find first character after first number after first character, ... Sort left, left mid, mid, right mid, right. I think we are gilding the lily. Chuck -- |
#23
|
|||
|
|||
Attn: Marshall Barton
Chuck wrote:
On Thu, 27 Sep 2007 12:38:23 -0500, Marshall Barton wrote: Chuck wrote: Due to the nature of the part numbers we are working with, parsing them left, center, right did not make any difference in the sort order. Your milage may vary for part numbers with different formats. I came very close a month ago, but there are some part numbers did not fit the simple parsing rules I used. As I see the problem, the trick is to identify which characters are in the left, middle and right portions given the apparently ambiguous mix of alphas, digits, the dot/dash and the fact that each portion can vary from 0 to 6 characters. There's even one example where the middle portion is not all digits. It's enough to give me a headache, but intriguing in a twisted sort of way. You can: find first number, find first character, find first number after first character, find first character after first number after first character, ... Sort left, left mid, mid, right mid, right. I think we are gilding the lily. I'm leaning in that direction, but I believe the problem is more complex than that because the left mid might really be the right mid if some other portions are missing. I don't think this weed is anything like a lily and gilding it might be more like trying to make a silk purse out of a sow's ear ;-) -- Marsh MVP [MS Access] |
#24
|
|||
|
|||
Attn: Marshall Barton
On Fri, 28 Sep 2007 00:11:10 -0500, Marshall Barton
wrote: Chuck wrote: On Thu, 27 Sep 2007 12:38:23 -0500, Marshall Barton wrote: Chuck wrote: Due to the nature of the part numbers we are working with, parsing them left, center, right did not make any difference in the sort order. Your milage may vary for part numbers with different formats. I came very close a month ago, but there are some part numbers did not fit the simple parsing rules I used. As I see the problem, the trick is to identify which characters are in the left, middle and right portions given the apparently ambiguous mix of alphas, digits, the dot/dash and the fact that each portion can vary from 0 to 6 characters. There's even one example where the middle portion is not all digits. It's enough to give me a headache, but intriguing in a twisted sort of way. You can: find first number, find first character, find first number after first character, find first character after first number after first character, ... Sort left, left mid, mid, right mid, right. I think we are gilding the lily. I'm leaning in that direction, but I believe the problem is more complex than that because the left mid might really be the right mid if some other portions are missing. I don't think this weed is anything like a lily and gilding it might be more like trying to make a silk purse out of a sow's ear ;-) Amen. |
#25
|
|||
|
|||
Attn: Marshall Barton
Marsh,
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. Thx Tom On Fri, 28 Sep 2007 00:11:10 -0500, Marshall Barton wrote: Chuck wrote: On Thu, 27 Sep 2007 12:38:23 -0500, Marshall Barton wrote: Chuck wrote: Due to the nature of the part numbers we are working with, parsing them left, center, right did not make any difference in the sort order. Your milage may vary for part numbers with different formats. I came very close a month ago, but there are some part numbers did not fit the simple parsing rules I used. As I see the problem, the trick is to identify which characters are in the left, middle and right portions given the apparently ambiguous mix of alphas, digits, the dot/dash and the fact that each portion can vary from 0 to 6 characters. There's even one example where the middle portion is not all digits. It's enough to give me a headache, but intriguing in a twisted sort of way. You can: find first number, find first character, find first number after first character, find first character after first number after first character, ... Sort left, left mid, mid, right mid, right. I think we are gilding the lily. I'm leaning in that direction, but I believe the problem is more complex than that because the left mid might really be the right mid if some other portions are missing. I don't think this weed is anything like a lily and gilding it might be more like trying to make a silk purse out of a sow's ear ;-) -- Tom |
#26
|
|||
|
|||
Attn: Marshall Barton
On Fri, 28 Sep 2007 13:27:04 -0400, Tom wrote:
Marsh, 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. Thx In the query that your report is based on, add a column: SortUDF:StandardizePartNum(your field name for Part Number) ie:SortUDF:StandardizePartNum(PartNum) I don't think spaces are allowed in this field name. Add to following code in the report module. ************* Public Function StandardizePartNum(your field name for part number) Dim k, j, m, x, y As Integer If IsNull(your field name for part number) Then StandardizePartNum = Null Exit Function End If 'find first Dot For k = 1 To Len(your field name for part number) If Mid(your field name for part number, k, 1) Like "." Then Exit For Next k 'find first Dash For j = 1 To Len(your field name for part number) If Mid(your field name for part number, j, 1) Like "-" Then Exit For Next j 'find first alpha character For m = 1 To Len(your field name for part number) If Not Mid(your field name for part number, m, 1) Like "#" Then Exit For Next m 'find first digit For x = 1 To Len(your field name for part number) If Mid(your field name for part number, x, 1) Like "#" Then Exit For Next x 'find next non-numeric character For y = x + 1 To Len(your field name for part number) If Not Mid(your field name for part number, y, 1) Like "#" Then Exit For Next y If m = 1 Then StandardizePartNum = Left(your field name for part number, x - 1) & Format(Val(Mid(your field name for part number, x)), "0000000") _ & Mid(your field name for part number, y) Exit Function End If If (m 1 And k Len(your field name for part number)) Then StandardizePartNum = Format(Left(your field name for part number, x - 1), "000000") & Format(Val(Mid(your field name for part number, x)), "0000000") _ & Mid(your field name for part number, y) Exit Function End If If (m 1 And j Len(your field name for part number)) Then StandardizePartNum = Format(Left(your field name for part number, x - 1), "00000") & Format(Val(Mid(your field name for part number, x)), "0000000") _ & Mid(your field name for part number, y) Exit Function End If If (m 1 And k Len(your field name for part number) And j Len(your field name for part number)) Then StandardizePartNum = Format(Left(your field name for part number, x - 1), "0000") & Format(Val(Mid(your field name for part number, x)), "0000000") _ & Mid(your field name for part number, y) Exit Function End If End Function **************** Open the report in design mode. Click on 'Sorting and Grouping' in to tool bar. In the first row of 'Field/Expression' add 'SortUDF' and set it to 'Ascending'. This is not a cure-all, but it comes close. Chuck -- |
#27
|
|||
|
|||
Attn: Marshall Barton
Tom wrote:
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 |
#28
|
|||
|
|||
Attn: Marshall Barton
On Sat, 29 Sep 2007 09:50:40 -0500, Marshall Barton
wrote: Tom wrote: 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. Marshall, Your equations produce a sort order that is different from mine but has similar characteristics. There are groups of part numbers that sort exactly like mine do, but the groups themselves are not in the same order. I like you equations better than mine because I think that your equations can handle a wider verity of part number formats than mine can. I'm going to work with your equations to see if I can move the part numbers beginning with numbers to the top of ther list. If either of us can do that, then I think Tom will be happy with your sort order. Chuck -- |
#29
|
|||
|
|||
Attn: Marshall Barton
Marsh,
Thx...will do. As for your Search suggestion, see my reply to Chuck below. It's not about finding a number in a series of numbers. It's about a shelf order corresponding to a Price Sheet Sequence and the resulting Access reports in that sequence. More as I know! Thx Tom On Sat, 29 Sep 2007 09:50:40 -0500, Marshall Barton wrote: 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 -- Tom |
#30
|
|||
|
|||
Attn: Marshall Barton
Marsh,
Some initial feedback: The latest code seems to handle my part number sequences perfectly, including the 1PG and 10PG series. It seems to sort a 24K record table perfectly at this point. More as I know. Yes, if I run a sort on the records that use a dash and a dot at the same time, they are mixed based on the left part of the number, e.g. a 10- comes before a 100. etc. However, this is not really an issue because I would never have to deal with both sets of numbers at the same time since they are different competitors. If I sort only the numbers with a dot, they all sort correctly! However... If I run a sort on a series of numbers that use a dash, there seems to be an issue. The sort is showing the following results: 10- then 20- then 30- then 40- then 50- the 60- then 763- then 80- then 90-. Three digit dashed numbers should come after two-digit dashed numbers. The mid section of the numbers is fine as well as the right section. Thx Tom On Sat, 29 Sep 2007 09:50:40 -0500, Marshall Barton wrote: Tom wrote: 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. -- Tom |
Thread Tools | |
Display Modes | |
|
|