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  

Attn: Marshall Barton



 
 
Thread Tools Display Modes
  #21  
Old September 27th, 2007, 06:38 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old September 27th, 2007, 10:21 PM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default 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  
Old September 28th, 2007, 06:11 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old September 28th, 2007, 12:37 PM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default 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  
Old September 28th, 2007, 06:27 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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  
Old September 29th, 2007, 01:40 AM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default 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  
Old September 29th, 2007, 03:50 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old September 30th, 2007, 01:42 PM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default 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  
Old September 30th, 2007, 01:57 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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  
Old September 30th, 2007, 03:35 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default 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

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 01:59 PM.


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