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
|
|||
|
|||
Sorting Question
Tom wrote:
Pls explain or point me to an explanation of a UDF. Thx! Just open a new module (using the database window) and Copy/Paste the code into the module. Since it is Public, you can call it from anywhere in Access. E.g. in the report's Sorting and Grouping Field/Expression: =StandardizePartNum([Comp Part]) Or, in an empty field in a query: Field Expr1: StandardizePartNum([Comp Part]) Sort Ascending Show unchecked (after debugging) -- Marsh MVP [MS Access] |
#22
|
|||
|
|||
Sorting Question
Thanks!
On Mon, 25 Jun 2007 19:19:28 -0500, Marshall Barton wrote: Tom wrote: Pls explain or point me to an explanation of a UDF. Thx! Just open a new module (using the database window) and Copy/Paste the code into the module. Since it is Public, you can call it from anywhere in Access. E.g. in the report's Sorting and Grouping Field/Expression: =StandardizePartNum([Comp Part]) Or, in an empty field in a query: Field Expr1: StandardizePartNum([Comp Part]) Sort Ascending Show unchecked (after debugging) -- Tom |
#23
|
|||
|
|||
Sorting Question
Thanks!
On Mon, 25 Jun 2007 19:49:00 -0400, John Spencer wrote: UDF = User defined function In this case a function written in VBA as Marshall did. '================================================ ==== John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================ ==== Tom wrote: Marshall, Pls explain or point me to an explanation of a UDF. Thx! Tom On Mon, 25 Jun 2007 07:34:48 -0500, Marshall Barton wrote: Here's an idea that might be easier to deal with. Create a UDF to translate your part numbers into a standard, sortable string. First, pad the initial alphas with spaces to make it a fixed length of say 5 (to allow for possible future longer alpha parts). Next, pad the numeric portion with leading 0s. Finally, add the trailing alphas. This function can then be used in the report's Sorting and Grouping. Because your explanations and examples are inconsistent, I am only making a wild stab at some of the code in the function: Public Function StandardizePartNum(PartNum) Dim k As Integer, j As Integer If IsNull(PartNum) Then StandardizePartNum = Null Exit Function End If 'find first digit For k = 1 To Len(PartNum) If Mid(PartNum, k, 1) Like "#" Then Exit For Next k 'find next non-numeric character For j = k + 1 To Len(PartNum) If Not Mid(PartNum, j, 1) Like "#" Then Exit For Next j StandardizePartNum = _ Left(PartNum, k - 1) & Space(6 - k) _ & Format(Val(Mid(PartNum, k)), "0000000") _ & Mid(PartNum, j) End Function -- Tom -- Tom |
#24
|
|||
|
|||
Sorting Question
Marshall,
The code below works 100% with my company's part numbers, but I found a couple of snafu's when dealing with some of our competitors part numbers. One series of numbers begins with a 52-, i.e. 52-125487 52-60554 The routine below places these types of part numbers in the order shown. Once the routine see the first non-numeric, it stops there. We have another competitor that uses numbers like: 100.01547 101.09874 102.55447 110.12345 I would assume this routine would go to the decimal point and stop. The good thing about this series of numbers is there is ALWAYS 5 places after the decimal, so this routine will still sort correctly. Any idea how to deal with my first example at the top? Thanks! On Mon, 25 Jun 2007 07:34:48 -0500, Marshall Barton wrote: Here's an idea that might be easier to deal with. Create a UDF to translate your part numbers into a standard, sortable string. First, pad the initial alphas with spaces to make it a fixed length of say 5 (to allow for possible future longer alpha parts). Next, pad the numeric portion with leading 0s. Finally, add the trailing alphas. This function can then be used in the report's Sorting and Grouping. Because your explanations and examples are inconsistent, I am only making a wild stab at some of the code in the function: Public Function StandardizePartNum(PartNum) Dim k As Integer, j As Integer If IsNull(PartNum) Then StandardizePartNum = Null Exit Function End If 'find first digit For k = 1 To Len(PartNum) If Mid(PartNum, k, 1) Like "#" Then Exit For Next k 'find next non-numeric character For j = k + 1 To Len(PartNum) If Not Mid(PartNum, j, 1) Like "#" Then Exit For Next j StandardizePartNum = _ Left(PartNum, k - 1) & Space(6 - k) _ & Format(Val(Mid(PartNum, k)), "0000000") _ & Mid(PartNum, j) End Function -- Tom |
#25
|
|||
|
|||
Sorting Question
Tom wrote:
The code below works 100% with my company's part numbers, but I found a couple of snafu's when dealing with some of our competitors part numbers. One series of numbers begins with a 52-, i.e. 52-125487 52-60554 The routine below places these types of part numbers in the order shown. Once the routine see the first non-numeric, it stops there. We have another competitor that uses numbers like: 100.01547 101.09874 102.55447 110.12345 When you are trying to parse a string into its component parts, it is essential that you come up with a rigorous set of rules that work for all possible cases. Then the problem becomes one of turning the set of rules into code. Based on your original examples, I came up with the rules: 1) The initial text will be from 0 to 5 non-numeric characters. 2) The numeric part can be up to 7 figits and immediately follows the intial text part. The numeric part may or may not be followed by a trailing text part that starts with any non-numeric character. 3) The training text part is the reamainder of the string starting with the first non-numeric character after the numeric part. Your new examples do not conform to those rules, so you must come up with a different set of rules. My **guess** for the rules is now: 1) The initial text will be from 0 to 3 characters ending with a non-numeric character. 2) The numeric part can be up to 7 figits and immediately follows the intial text part. The numeric part may or may not be followed by a trailing text part that starts with any non-numeric character. 3) The trailing text part is any reamainder of the string starting with the first non-numeric character after the numeric part. You must decide if my guesses at the rules is valid for all of your various situations. Pay particular attention to the 3 that I used. If you might get very short strings (e.g. X3 or just 12) then you will need to modify rule 1) and the code to accomodate the very short strings. With all that said, here's my guess at a new procedu Public Function StandardizePartNum(PartNum) Dim k As Integer, j As Integer If IsNull(PartNum) Then StandardizePartNum = Null Exit Function End If 'find last non-digit For k = 3 To 1 Step -1 If Not Mid(PartNum, k, 1) Like "#" Then Exit For Next k 'find numeric portion For j = k + 1 To Len(PartNum) If Not Mid(PartNum, j, 1) Like "#" Then Exit For Next j StandardizePartNum = _ Left(PartNum, k) & Space(4 - k) _ & Format(Val(Mid(PartNum, k + 1)), "0000000") _ & Mid(PartNum, j) End Function -- Marsh MVP [MS Access] |
#26
|
|||
|
|||
Sorting Question
On Fri, 29 Jun 2007 13:13:04 -0400, Tom wrote:
Marshall, The code below works 100% with my company's part numbers, but I found a couple of snafu's when dealing with some of our competitors part numbers. One series of numbers begins with a 52-, i.e. 52-125487 52-60554 The routine below places these types of part numbers in the order shown. Once the routine see the first non-numeric, it stops there. We have another competitor that uses numbers like: 100.01547 101.09874 102.55447 110.12345 I would assume this routine would go to the decimal point and stop. The good thing about this series of numbers is there is ALWAYS 5 places after the decimal, so this routine will still sort correctly. Any idea how to deal with my first example at the top? Thanks! On Mon, 25 Jun 2007 07:34:48 -0500, Marshall Barton wrote: Here's an idea that might be easier to deal with. Create a UDF to translate your part numbers into a standard, sortable string. First, pad the initial alphas with spaces to make it a fixed length of say 5 (to allow for possible future longer alpha parts). Next, pad the numeric portion with leading 0s. Finally, add the trailing alphas. This function can then be used in the report's Sorting and Grouping. Because your explanations and examples are inconsistent, I am only making a wild stab at some of the code in the function: Public Function StandardizePartNum(PartNum) Dim k As Integer, j As Integer If IsNull(PartNum) Then StandardizePartNum = Null Exit Function End If 'find first digit For k = 1 To Len(PartNum) If Mid(PartNum, k, 1) Like "#" Then Exit For Next k 'find next non-numeric character For j = k + 1 To Len(PartNum) If Not Mid(PartNum, j, 1) Like "#" Then Exit For Next j StandardizePartNum = _ Left(PartNum, k - 1) & Space(6 - k) _ & Format(Val(Mid(PartNum, k)), "0000000") _ & Mid(PartNum, j) End Function Inspect Marshall's function. The function is perfect for the conditions you originally stated. Study the function to see what it is doing. Then make a small addition to take care of part numbers similar to 52-123456. Chuck -- |
#27
|
|||
|
|||
Sorting Question
Marshall,
I think this has got it. Let me do some more testing with various number format and will report shortly. Thanks for your help...much appreciated! Tom On Fri, 29 Jun 2007 14:37:25 -0500, Marshall Barton wrote: Tom wrote: The code below works 100% with my company's part numbers, but I found a couple of snafu's when dealing with some of our competitors part numbers. One series of numbers begins with a 52-, i.e. 52-125487 52-60554 The routine below places these types of part numbers in the order shown. Once the routine see the first non-numeric, it stops there. We have another competitor that uses numbers like: 100.01547 101.09874 102.55447 110.12345 When you are trying to parse a string into its component parts, it is essential that you come up with a rigorous set of rules that work for all possible cases. Then the problem becomes one of turning the set of rules into code. Based on your original examples, I came up with the rules: 1) The initial text will be from 0 to 5 non-numeric characters. 2) The numeric part can be up to 7 figits and immediately follows the intial text part. The numeric part may or may not be followed by a trailing text part that starts with any non-numeric character. 3) The training text part is the reamainder of the string starting with the first non-numeric character after the numeric part. Your new examples do not conform to those rules, so you must come up with a different set of rules. My **guess** for the rules is now: 1) The initial text will be from 0 to 3 characters ending with a non-numeric character. 2) The numeric part can be up to 7 figits and immediately follows the intial text part. The numeric part may or may not be followed by a trailing text part that starts with any non-numeric character. 3) The trailing text part is any reamainder of the string starting with the first non-numeric character after the numeric part. You must decide if my guesses at the rules is valid for all of your various situations. Pay particular attention to the 3 that I used. If you might get very short strings (e.g. X3 or just 12) then you will need to modify rule 1) and the code to accomodate the very short strings. With all that said, here's my guess at a new procedu Public Function StandardizePartNum(PartNum) Dim k As Integer, j As Integer If IsNull(PartNum) Then StandardizePartNum = Null Exit Function End If 'find last non-digit For k = 3 To 1 Step -1 If Not Mid(PartNum, k, 1) Like "#" Then Exit For Next k 'find numeric portion For j = k + 1 To Len(PartNum) If Not Mid(PartNum, j, 1) Like "#" Then Exit For Next j StandardizePartNum = _ Left(PartNum, k) & Space(4 - k) _ & Format(Val(Mid(PartNum, k + 1)), "0000000") _ & Mid(PartNum, j) End Function -- Tom |
#28
|
|||
|
|||
Sorting Question
Marshall,
I could not break this one. Thanks for all your efforts and support! Tom On Sat, 30 Jun 2007 16:45:39 -0400, Tom wrote: Marshall, I think this has got it. Let me do some more testing with various number format and will report shortly. Thanks for your help...much appreciated! Tom On Fri, 29 Jun 2007 14:37:25 -0500, Marshall Barton wrote: Tom wrote: The code below works 100% with my company's part numbers, but I found a couple of snafu's when dealing with some of our competitors part numbers. One series of numbers begins with a 52-, i.e. 52-125487 52-60554 The routine below places these types of part numbers in the order shown. Once the routine see the first non-numeric, it stops there. We have another competitor that uses numbers like: 100.01547 101.09874 102.55447 110.12345 When you are trying to parse a string into its component parts, it is essential that you come up with a rigorous set of rules that work for all possible cases. Then the problem becomes one of turning the set of rules into code. Based on your original examples, I came up with the rules: 1) The initial text will be from 0 to 5 non-numeric characters. 2) The numeric part can be up to 7 figits and immediately follows the intial text part. The numeric part may or may not be followed by a trailing text part that starts with any non-numeric character. 3) The training text part is the reamainder of the string starting with the first non-numeric character after the numeric part. Your new examples do not conform to those rules, so you must come up with a different set of rules. My **guess** for the rules is now: 1) The initial text will be from 0 to 3 characters ending with a non-numeric character. 2) The numeric part can be up to 7 figits and immediately follows the intial text part. The numeric part may or may not be followed by a trailing text part that starts with any non-numeric character. 3) The trailing text part is any reamainder of the string starting with the first non-numeric character after the numeric part. You must decide if my guesses at the rules is valid for all of your various situations. Pay particular attention to the 3 that I used. If you might get very short strings (e.g. X3 or just 12) then you will need to modify rule 1) and the code to accomodate the very short strings. With all that said, here's my guess at a new procedu Public Function StandardizePartNum(PartNum) Dim k As Integer, j As Integer If IsNull(PartNum) Then StandardizePartNum = Null Exit Function End If 'find last non-digit For k = 3 To 1 Step -1 If Not Mid(PartNum, k, 1) Like "#" Then Exit For Next k 'find numeric portion For j = k + 1 To Len(PartNum) If Not Mid(PartNum, j, 1) Like "#" Then Exit For Next j StandardizePartNum = _ Left(PartNum, k) & Space(4 - k) _ & Format(Val(Mid(PartNum, k + 1)), "0000000") _ & Mid(PartNum, j) End Function -- Tom |
Thread Tools | |
Display Modes | |
|
|