A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sorting Question



 
 
Thread Tools Display Modes
  #21  
Old June 26th, 2007, 01:19 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old June 26th, 2007, 01:40 AM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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  
Old June 26th, 2007, 01:40 AM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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  
Old June 29th, 2007, 06:13 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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  
Old June 29th, 2007, 08:37 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old June 30th, 2007, 03:34 AM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default 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  
Old June 30th, 2007, 09:45 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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  
Old July 2nd, 2007, 10:35 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.