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
  #11  
Old September 25th, 2007, 08:39 PM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default Attn: Marshall Barton

On Mon, 24 Sep 2007 18:15:41 -0400, Tom wrote:

Chuck,

Here that file with everything sorted.

Thanks

Part Num,L,M,R
1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
111.1,###a,#,s
111.11,###a,##,s
111.111,###a,###,s
111.1111,###a,####,s
111.11111,###a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
11-11A12,##a,##,a##
11-111A12,##a,###,a##
111-1,###a,#,s
111-11,###a,##,s
111-111,###a,###,s
111-1111,###a,####,s
111-1111A,###a,####,a
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111,###a,#####,s
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-11111AAA,###a,#####,aaa
111-D1,###a,a#,s
111-D11,###a,a##,s
111-D111,###a,a###,s
111-D1111,###a,a####,s
11A1,##a,#,s
11A11,##a,##,s
11A11A,##a,##,a
11A11AA,##a,##,aa
11A111,##a,###,s
11A111A,##a,###,a
11A111AA,##a,###,aa
11A1111,##a,####,s
11A1111A,##a,####,a
11A1111AA,##a,####,aa
15-1,##a,#,s
15-11,##a,##,s
15-11A,##a,##,a
15-111,##a,###,s
15-111A,##a,###,a
15-1111,##a,####,s
15-1111A,##a,####,a
15-1111AA,##a,####,aa
15-11111,##a,#####,s
15-11111A,##a,#####,a
15-111111,##a,######,s
15-111111A,##a,######,a
1PG,s,#,aa
11,s,##,s
11N,s,##,a
11PG,s,##,aa
33APG,s,##,aaa
111,s,###,s
111APG,s,###,aaa
111N,s,###,a
111PG,s,###,aa
1111,s,####,s
1111APG,s,####,aaa
1111PL,s,####,aa
1111PLA,s,####,aaa
1111R,s,####,a
11111,s,#####,s
11111PL,s,#####,aa
11111PLA,s,#####,aaa
11111R,s,#####,a
111111,s,######,s
1111111,s,#######,s
1111111A,s,#######,a
111111PL,s,#######,aa
111111PLA,s,#######,aaa
111111R,s,#######,a
11111111,s,########,s
761-1,aaaa,#,s
761-11,aaaa,##,s
761-111,aaaa,###,s
761-1111,aaaa,####,s
761-1111A,aaaa,####,a
761-1111AA,aaaa,####,aa
761-11111,aaaa,#####,s
761-1111111,aaaa,#######,s
B-111,aa,###,s
B-1111,aa,####,s
BAL-111,aaaa,###,s
BD1,aa,#,s
BD1M,aa,#,a
BD11,aa,##,s
BD11BM,aa,##,aa
BD11M,aa,##,a
BD11ABC,aa,##,aaa
BD111,aa,###,s
BD111BM,aa,###,aa
BD111M,aa,###,a
BD1111,aa,####,s
BD1111AM,aa,####,aa
BD1111C,aa,####,a
H11,a,##,s
H11A,a,##,a
H111,a,###,s
H111-2,a,###,a#
H111A,a,###,a
H111AC,a,###,aa
H1111,a,####,s
H1111-2,a,####,a#
H1111A,a,####,a
H1111AC,a,####,aa
H11111,a,#####,s
H11111-2,a,#####,a#
H11111A,a,#####,a
H11111AA,a,#####,aa
H111111,a,######,s
H111111A,a,######,a
H111111R,a,######,a
HP-11111.1,aaa,#####,a#
MC11111,aa,#####,s
MC111111,aa,######,s
MC111111A,aa,######,a
PGD1,aaa,#,s
PGD1M,aaa,#,a
PGD11,aaa,##,s
PGD11BM,aaa,##,aa
PGD11BMA,aaa,##,aaa
PGD11M,aaa,##,a
PGD111,aaa,###,s
PGD111BM,aaa,###,aa
PGD111BMA,aaa,###,aaa
PGD111M,aaa,###,a
PGD1111,aaa,####,s
PGD1111AM,aaa,####,aa
PGD1111AMA,aaa,####,aaa
PGD1111C,aaa,####,a
PGD11111,aaa,#####,s
PGD111111,aaa,######,s
RC1111QS,aa,####,aa
RC11111A,aa,#####,a
RC11111ANS,aa,#####,aaa
RC11111NS,aa,#####,aa
SNA6A24D,aaa,#,a##a
SNA6A2D,aaa,#,a#a
SSRD2,aaaa,#,s
SSRD52,aaaa,##,s
SSRD52M,aaaa,##,a
SSRD552,aaaa,###,s
SSRD552M,aaaa,###,a
SSRD5521,aaaa,####,s
SSRD5521M,aaaa,####,a
T1PG,a,#,aa
T11PG,a,##,aa
T33APG,a,##,aaa
T111APG,a,###,aaa
T111PG,a,###,aa
T1111APG,a,####,aaa
T11111APG,a,#####,aaa
T11111PG,a,#####,aa
TSSRD20,aaaaa,##,s
TSSRD20A,aaaaa,##,a
TSSRD200,aaaaa,###,s
TSSRD200A,aaaaa,###,a
,,,
whe,,,
,# is any digit,,
,a is any nondigit,,
,s is just spaces (i.e empty),,


On Sun, 23 Sep 2007 17:39:49 -0400, Chuck wrote:

On Sun, 23 Sep 2007 16:37:25 -0400, Tom wrote:

Chuck,

No, the part numbers are not interchangeable.

Yes, they are from other organizations as well as my own.

Not sure what you mean by unbreakable UDF.

Thanks

Big snip
Would you please re-post your list of Part Num(s) sorted exactly the way you
want them. Possibly with the *answer* to your question, a set of equations can
be devised that will produce that *answer*.

Chuck


Sorting as above looks straight forward, but it isn't. You want to sort
111.11111 before 1-1. It is possible, but will require more complex equations
than I'm willing to spend the time developing. When you get to the second
character and it is not a period or dash, you have to examine the next several
characters to see if there is a period or dash latter on in the sequence. Also,
what happens when someone starts using / \ ! ; : or , for separators? (by the
way, dashes are naturally sorted before periods) There are probably others
reading this news group who are a lot smarter than I am and who can write the
equations "of the top of the head'.

Sorting as below is quite easy. Notice that all the 1. and 1- are sorted
before Part Numbers that start with several numeric characters.

1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
1-123-1,#a,###,a#
11,s,##,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
11-111A12,##a,###,a##
11-11A12,##a,##,a##
111,s,###,s
111.1,###a,#,s
111.111,###a,###,s
111.11111,###a,#####,s
111.1111,###a,####,s
111.11,###a,##,s
111-1,###a,#,s
111-1111,###a,####,s
111-111,###a,###,s
111-11,###a,##,s
111-1111A,###a,####,a
111-11111,###a,#####,s
111-11111AAA,###a,#####,aaa
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-D111,###a,a###,s
111-D1111,###a,a####,s
111-D11,###a,a##,s
111-D1,###a,a#,s
1111,s,####,s
11111,s,#####,s
111111,s,######,s
1111111,s,#######,s
1111111A,s,#######,a
111111R,s,#######,a
111111PL,s,#######,aa
111111PLA,s,#######,aaa
11111111,s,########,s
11111PL,s,#####,aa
11111PLA,s,#####,aaa
11111R,s,#####,a
1111APG,s,####,aaa
1111PLA,s,####,aaa
1111PL,s,####,aa
1111R,s,####,a
111APG,s,###,aaa
111N,s,###,a
111PG,s,###,aa
11A1,##a,#,s
11A11,##a,##,s
11A1111AA,##a,####,aa
11A111,##a,###,s
11A111AA,##a,###,aa
11A1111,##a,####,s
11A1111A,##a,####,a
11A111A,##a,###,a
11A11AA,##a,##,aa
11A11A,##a,##,a
11N,s,##,a
11PG,s,##,aa
14-123-12,##a,###,a##
15-1,##a,#,s
15-11,##a,##,s
15-11111,##a,#####,s
15-111,##a,###,s
15-111A,##a,###,a
15-1111,##a,####,s
15-1111AA,##a,####,aa
15-11111A,##a,#####,a
15-111111,##a,######,s
15-111111A,##a,######,a
15-1111A,##a,####,a
15-11A,##a,##,a
1PG,s,#,aa
33APG,s,##,aaa
761-1,aaaa,#,s
761-11,aaaa,##,s
761-111,aaaa,###,s
761-1111,aaaa,####,s
761-1111A,aaaa,####,a
761-1111AA,aaaa,####,aa
761-11111,aaaa,#####,s
761-1111111,aaaa,#######,s
B-111,aa,###,s
B-1111,aa,####,s
BAL-111,aaaa,###,s
BD1,aa,#,s
BD11,aa,##,s
BD111,aa,###,s
BD1111C,aa,####,a
BD1111AM,aa,####,aa
BD1111,aa,####,s
BD111BM,aa,###,aa
BD111M,aa,###,a
BD11BM,aa,##,aa
BD11M,aa,##,a
BD1M,aa,#,a
BS11ABC,aa,##,aaa
H11,a,##,s
H111,a,###,s
H111-2,a,###,a#
H1111,a,####,s
H1111-2,a,####,a#
H111111R,a,######,a
H11111,a,#####,s
H11111-2,a,#####,a#
H11111AA,a,#####,aa
H111111,a,######,s
H111111A,a,######,a
H11111A,a,#####,a
H1111A,a,####,a
H1111AC,a,####,aa
H111A,a,###,a
H111AC,a,###,aa
H11A,a,##,a
HP-11111.1,aaa,#####,a#
MC111111A,aa,######,a
MC111111,aa,######,s
MC11111,aa,#####,s
PGD1,aaa,#,s
PGD11,aaa,##,s
PGD111BM,aaa,###,aa
PGD111,aaa,###,s
PGD111BMA,aaa,###,aaa
PGD111M,aaa,###,a
PGD1111,aaa,####,s
PGD1111C,aaa,####,a
PGD1111AM,aaa,####,aa
PGD1111AMA,aaa,####,aaa
PGD11111,aaa,#####,s
PGD111111,aaa,######,s
PGD11BM,aaa,##,aa
PGD11BMA,aaa,##,aaa
PGD11M,aaa,##,a
PGD1M,aaa,#,a
RC11111ANS,aa,#####,aaa
RC11111A,aa,#####,a
RC11111NS,aa,#####,aa
RC1111QS,aa,####,aa
SNA6A24D,aaa,#,a##a
SNA6A2D,aaa,#,a#a
SSRD2,aaaa,#,s
SSRD52,aaaa,##,s
SSRD52M,aaaa,##,a
SSRD552,aaaa,###,s
SSRD5521M,aaaa,####,a
SSRD5521,aaaa,####,s
SSRD552M,aaaa,###,a
T11111APG,a,#####,aaa
T11111PG,a,#####,aa
T1111APG,a,####,aaa
T111APG,a,###,aaa
T111PG,a,###,aa
T11PG,a,##,aa
T1PG,a,#,aa
T33APG,a,##,aaa
TSSRD200A,aaaaa,###,a
TSSRD20A,aaaaa,##,a
TSSRD20,aaaaa,##,s
TSSRD200,aaaaa,###,s

  #12  
Old September 25th, 2007, 10:12 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default Attn: Marshall Barton

On Tue, 25 Sep 2007 15:39:56 -0400, Chuck wrote:

On Mon, 24 Sep 2007 18:15:41 -0400, Tom wrote:

Chuck,

Here that file with everything sorted.

Thanks

Part Num,L,M,R
1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
111.1,###a,#,s
111.11,###a,##,s
111.111,###a,###,s
111.1111,###a,####,s
111.11111,###a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
11-11A12,##a,##,a##
11-111A12,##a,###,a##
111-1,###a,#,s
111-11,###a,##,s
111-111,###a,###,s
111-1111,###a,####,s
111-1111A,###a,####,a
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111,###a,#####,s
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-11111AAA,###a,#####,aaa
111-D1,###a,a#,s
111-D11,###a,a##,s
111-D111,###a,a###,s
111-D1111,###a,a####,s
11A1,##a,#,s
11A11,##a,##,s
11A11A,##a,##,a
11A11AA,##a,##,aa
11A111,##a,###,s
11A111A,##a,###,a
11A111AA,##a,###,aa
11A1111,##a,####,s
11A1111A,##a,####,a
11A1111AA,##a,####,aa
15-1,##a,#,s
15-11,##a,##,s
15-11A,##a,##,a
15-111,##a,###,s
15-111A,##a,###,a
15-1111,##a,####,s
15-1111A,##a,####,a
15-1111AA,##a,####,aa
15-11111,##a,#####,s
15-11111A,##a,#####,a
15-111111,##a,######,s
15-111111A,##a,######,a
1PG,s,#,aa
11,s,##,s
11N,s,##,a
11PG,s,##,aa
33APG,s,##,aaa
111,s,###,s
111APG,s,###,aaa
111N,s,###,a
111PG,s,###,aa
1111,s,####,s
1111APG,s,####,aaa
1111PL,s,####,aa
1111PLA,s,####,aaa
1111R,s,####,a
11111,s,#####,s
11111PL,s,#####,aa
11111PLA,s,#####,aaa
11111R,s,#####,a
111111,s,######,s
1111111,s,#######,s
1111111A,s,#######,a
111111PL,s,#######,aa
111111PLA,s,#######,aaa
111111R,s,#######,a
11111111,s,########,s
761-1,aaaa,#,s
761-11,aaaa,##,s
761-111,aaaa,###,s
761-1111,aaaa,####,s
761-1111A,aaaa,####,a
761-1111AA,aaaa,####,aa
761-11111,aaaa,#####,s
761-1111111,aaaa,#######,s
B-111,aa,###,s
B-1111,aa,####,s
BAL-111,aaaa,###,s
BD1,aa,#,s
BD1M,aa,#,a
BD11,aa,##,s
BD11BM,aa,##,aa
BD11M,aa,##,a
BD11ABC,aa,##,aaa
BD111,aa,###,s
BD111BM,aa,###,aa
BD111M,aa,###,a
BD1111,aa,####,s
BD1111AM,aa,####,aa
BD1111C,aa,####,a
H11,a,##,s
H11A,a,##,a
H111,a,###,s
H111-2,a,###,a#
H111A,a,###,a
H111AC,a,###,aa
H1111,a,####,s
H1111-2,a,####,a#
H1111A,a,####,a
H1111AC,a,####,aa
H11111,a,#####,s
H11111-2,a,#####,a#
H11111A,a,#####,a
H11111AA,a,#####,aa
H111111,a,######,s
H111111A,a,######,a
H111111R,a,######,a
HP-11111.1,aaa,#####,a#
MC11111,aa,#####,s
MC111111,aa,######,s
MC111111A,aa,######,a
PGD1,aaa,#,s
PGD1M,aaa,#,a
PGD11,aaa,##,s
PGD11BM,aaa,##,aa
PGD11BMA,aaa,##,aaa
PGD11M,aaa,##,a
PGD111,aaa,###,s
PGD111BM,aaa,###,aa
PGD111BMA,aaa,###,aaa
PGD111M,aaa,###,a
PGD1111,aaa,####,s
PGD1111AM,aaa,####,aa
PGD1111AMA,aaa,####,aaa
PGD1111C,aaa,####,a
PGD11111,aaa,#####,s
PGD111111,aaa,######,s
RC1111QS,aa,####,aa
RC11111A,aa,#####,a
RC11111ANS,aa,#####,aaa
RC11111NS,aa,#####,aa
SNA6A24D,aaa,#,a##a
SNA6A2D,aaa,#,a#a
SSRD2,aaaa,#,s
SSRD52,aaaa,##,s
SSRD52M,aaaa,##,a
SSRD552,aaaa,###,s
SSRD552M,aaaa,###,a
SSRD5521,aaaa,####,s
SSRD5521M,aaaa,####,a
T1PG,a,#,aa
T11PG,a,##,aa
T33APG,a,##,aaa
T111APG,a,###,aaa
T111PG,a,###,aa
T1111APG,a,####,aaa
T11111APG,a,#####,aaa
T11111PG,a,#####,aa
TSSRD20,aaaaa,##,s
TSSRD20A,aaaaa,##,a
TSSRD200,aaaaa,###,s
TSSRD200A,aaaaa,###,a
,,,
whe,,,
,# is any digit,,
,a is any nondigit,,
,s is just spaces (i.e empty),,


On Sun, 23 Sep 2007 17:39:49 -0400, Chuck wrote:

On Sun, 23 Sep 2007 16:37:25 -0400, Tom wrote:

Chuck,

No, the part numbers are not interchangeable.

Yes, they are from other organizations as well as my own.

Not sure what you mean by unbreakable UDF.

Thanks

Big snip
Would you please re-post your list of Part Num(s) sorted exactly the way you
want them. Possibly with the *answer* to your question, a set of equations can
be devised that will produce that *answer*.

Chuck


Sorting as above looks straight forward, but it isn't. You want to sort
111.11111 before 1-1. It is possible, but will require more complex equations
than I'm willing to spend the time developing. When you get to the second
character and it is not a period or dash, you have to examine the next several
characters to see if there is a period or dash latter on in the sequence. Also,
what happens when someone starts using / \ ! ; : or , for separators? (by the
way, dashes are naturally sorted before periods) There are probably others
reading this news group who are a lot smarter than I am and who can write the
equations "of the top of the head'.

Sorting as below is quite easy. Notice that all the 1. and 1- are sorted
before Part Numbers that start with several numeric characters.

1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
1-123-1,#a,###,a#
11,s,##,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
11-111A12,##a,###,a##
11-11A12,##a,##,a##
111,s,###,s
111.1,###a,#,s
111.111,###a,###,s
111.11111,###a,#####,s
111.1111,###a,####,s
111.11,###a,##,s
111-1,###a,#,s
111-1111,###a,####,s
111-111,###a,###,s
111-11,###a,##,s
111-1111A,###a,####,a
111-11111,###a,#####,s
111-11111AAA,###a,#####,aaa
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-D111,###a,a###,s
111-D1111,###a,a####,s
111-D11,###a,a##,s
111-D1,###a,a#,s
1111,s,####,s
11111,s,#####,s
111111,s,######,s
1111111,s,#######,s
1111111A,s,#######,a
111111R,s,#######,a
111111PL,s,#######,aa
111111PLA,s,#######,aaa
11111111,s,########,s
11111PL,s,#####,aa
11111PLA,s,#####,aaa
11111R,s,#####,a
1111APG,s,####,aaa
1111PLA,s,####,aaa
1111PL,s,####,aa
1111R,s,####,a
111APG,s,###,aaa
111N,s,###,a
111PG,s,###,aa
11A1,##a,#,s
11A11,##a,##,s
11A1111AA,##a,####,aa
11A111,##a,###,s
11A111AA,##a,###,aa
11A1111,##a,####,s
11A1111A,##a,####,a
11A111A,##a,###,a
11A11AA,##a,##,aa
11A11A,##a,##,a
11N,s,##,a
11PG,s,##,aa
14-123-12,##a,###,a##
15-1,##a,#,s
15-11,##a,##,s
15-11111,##a,#####,s
15-111,##a,###,s
15-111A,##a,###,a
15-1111,##a,####,s
15-1111AA,##a,####,aa
15-11111A,##a,#####,a
15-111111,##a,######,s
15-111111A,##a,######,a
15-1111A,##a,####,a
15-11A,##a,##,a
1PG,s,#,aa
33APG,s,##,aaa
761-1,aaaa,#,s
761-11,aaaa,##,s
761-111,aaaa,###,s
761-1111,aaaa,####,s
761-1111A,aaaa,####,a
761-1111AA,aaaa,####,aa
761-11111,aaaa,#####,s
761-1111111,aaaa,#######,s
B-111,aa,###,s
B-1111,aa,####,s
BAL-111,aaaa,###,s
BD1,aa,#,s
BD11,aa,##,s
BD111,aa,###,s
BD1111C,aa,####,a
BD1111AM,aa,####,aa
BD1111,aa,####,s
BD111BM,aa,###,aa
BD111M,aa,###,a
BD11BM,aa,##,aa
BD11M,aa,##,a
BD1M,aa,#,a
BS11ABC,aa,##,aaa
H11,a,##,s
H111,a,###,s
H111-2,a,###,a#
H1111,a,####,s
H1111-2,a,####,a#
H111111R,a,######,a
H11111,a,#####,s
H11111-2,a,#####,a#
H11111AA,a,#####,aa
H111111,a,######,s
H111111A,a,######,a
H11111A,a,#####,a
H1111A,a,####,a
H1111AC,a,####,aa
H111A,a,###,a
H111AC,a,###,aa
H11A,a,##,a
HP-11111.1,aaa,#####,a#
MC111111A,aa,######,a
MC111111,aa,######,s
MC11111,aa,#####,s
PGD1,aaa,#,s
PGD11,aaa,##,s
PGD111BM,aaa,###,aa
PGD111,aaa,###,s
PGD111BMA,aaa,###,aaa
PGD111M,aaa,###,a
PGD1111,aaa,####,s
PGD1111C,aaa,####,a
PGD1111AM,aaa,####,aa
PGD1111AMA,aaa,####,aaa
PGD11111,aaa,#####,s
PGD111111,aaa,######,s
PGD11BM,aaa,##,aa
PGD11BMA,aaa,##,aaa
PGD11M,aaa,##,a
PGD1M,aaa,#,a
RC11111ANS,aa,#####,aaa
RC11111A,aa,#####,a
RC11111NS,aa,#####,aa
RC1111QS,aa,####,aa
SNA6A24D,aaa,#,a##a
SNA6A2D,aaa,#,a#a
SSRD2,aaaa,#,s
SSRD52,aaaa,##,s
SSRD52M,aaaa,##,a
SSRD552,aaaa,###,s
SSRD5521M,aaaa,####,a
SSRD5521,aaaa,####,s
SSRD552M,aaaa,###,a
T11111APG,a,#####,aaa
T11111PG,a,#####,aa
T1111APG,a,####,aaa
T111APG,a,###,aaa
T111PG,a,###,aa
T11PG,a,##,aa
T1PG,a,#,aa
T33APG,a,##,aaa
TSSRD200A,aaaaa,###,a
TSSRD20A,aaaaa,##,a
TSSRD20,aaaaa,##,s
TSSRD200,aaaaa,###,s


Chuck,

Am not suggesting sort in an unnatural order, so sort the dot before the
dash.

what happens when someone starts using / \ ! ; : or , for separators?


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.

Thanks!
--
Tom

  #13  
Old September 26th, 2007, 05:27 AM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default Attn: Marshall Barton

On Tue, 25 Sep 2007 17:12:54 -0400, Tom wrote:

On Tue, 25 Sep 2007 15:39:56 -0400, Chuck wrote:

On Mon, 24 Sep 2007 18:15:41 -0400, Tom wrote:

Chuck,

Here that file with everything sorted.

Thanks

Part Num,L,M,R
1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
111.1,###a,#,s
111.11,###a,##,s
111.111,###a,###,s
111.1111,###a,####,s
111.11111,###a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
11-11A12,##a,##,a##
11-111A12,##a,###,a##
111-1,###a,#,s
111-11,###a,##,s
111-111,###a,###,s
111-1111,###a,####,s
111-1111A,###a,####,a
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111,###a,#####,s
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-11111AAA,###a,#####,aaa



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.

Thanks!


Yes, but anything with a dot anywhere in the sequence is sorted before anything
with a dash anywhere in the sequence. Hence 111. is sorted before 1-

Your UDF (User Defined Function) ("Public Function
StandardizePartNum(PartNum)") (as written) will not handle this. You need
equations that first look for dots, dashes, and neither drops nor dashes. Then
each group must be handled separately with its own UDF

As I said, possible, but complex equations.

Chuck
--
  #14  
Old September 26th, 2007, 05:49 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Attn: Marshall Barton

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

--
Marsh
MVP [MS Access]
  #15  
Old September 26th, 2007, 07:30 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default Attn: Marshall Barton

Marsh,

Thanks for the update.

Technically, I could do without all those you mention below save the 1PG
which is a part of my product line. If this helps, then by all means
ignore the other ones.

Thx

Tom

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

--
Tom

  #16  
Old September 26th, 2007, 07:49 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default Attn: Marshall Barton

Chuck,

It is OK for the dot numbers to come before the dashed number as they would
be in a series on the shelf in the correct order as shown. As long as the
1. come before the 11. before the 111. and the numbers after the dot start
with .1 then a .11 then a .111, etc. Same goes for the dashed numbers.

I also do not have to deal with every type of number all the time except
for my own. What I have tried to do is build a table of numbers that would
cover most if not all situations. I spent a week looking at every possible
number I could deal with and I believe they are all represented by the
table.

Marsh has commented there are some difficult number patterns to deal with.
These can be skipped for the more likely one, i.e. part numbers in my own
product line I am hoping.

Hope this info helps a bit.

Thanks

Tom



On Wed, 26 Sep 2007 00:27:07 -0400, Chuck wrote:

On Tue, 25 Sep 2007 17:12:54 -0400, Tom wrote:

On Tue, 25 Sep 2007 15:39:56 -0400, Chuck wrote:

On Mon, 24 Sep 2007 18:15:41 -0400, Tom wrote:

Chuck,

Here that file with everything sorted.

Thanks

Part Num,L,M,R
1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
111.1,###a,#,s
111.11,###a,##,s
111.111,###a,###,s
111.1111,###a,####,s
111.11111,###a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
11-11A12,##a,##,a##
11-111A12,##a,###,a##
111-1,###a,#,s
111-11,###a,##,s
111-111,###a,###,s
111-1111,###a,####,s
111-1111A,###a,####,a
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111,###a,#####,s
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-11111AAA,###a,#####,aaa



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.

Thanks!


Yes, but anything with a dot anywhere in the sequence is sorted before anything
with a dash anywhere in the sequence. Hence 111. is sorted before 1-

Your UDF (User Defined Function) ("Public Function
StandardizePartNum(PartNum)") (as written) will not handle this. You need
equations that first look for dots, dashes, and neither drops nor dashes. Then
each group must be handled separately with its own UDF

As I said, possible, but complex equations.

Chuck

--
Tom

  #17  
Old September 27th, 2007, 12:31 AM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default Attn: Marshall Barton

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
  #18  
Old September 27th, 2007, 06:35 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Attn: Marshall Barton

Chuck wrote:

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.



I don't see how just adding some 0s can do the job. There
are really three separate portions to these insidious part
numbers, kind of like sorting on three fields. My idea is
to parse out the separate portions (left, middle and right)
and combine them into a canonical form with fixed length
portions that can be sorted normally.

E.g.
1F2 1D----0000002-------
D2A D-----0000002A-------
DJ2A DJ----0000002A-------
DJ3BB DJ----0000003BB------
. . .

If only the real part numbers were so easily parsed into the
three portions.

--
Marsh
MVP [MS Access]
  #19  
Old September 27th, 2007, 02:39 PM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default Attn: Marshall Barton

On Thu, 27 Sep 2007 00:35:26 -0500, Marshall Barton
wrote:

I don't see how just adding some 0s can do the job. There
are really three separate portions to these insidious part
numbers, kind of like sorting on three fields. My idea is
to parse out the separate portions (left, middle and right)
and combine them into a canonical form with fixed length
portions that can be sorted normally.

E.g.
1F2 1D----0000002-------
D2A D-----0000002A-------
DJ2A DJ----0000002A-------
DJ3BB DJ----0000003BB------
. . .

If only the real part numbers were so easily parsed into the
three portions.


Marshall,

All the zeroes do is to force the part numbers that use a dot separator to be
sorted before anything else, then the numbers with dash separators to be sorted
after the dots and before part numbers with neither dots nor dashes.

The real part numbers may not need to be parsed. However, I will parse out
the left, middle, and right portions of the part numbers and format the left
portions with a different number of places for each group (dots, dashes,
neither). Let you know how it works out.

Chuck
--

  #20  
Old September 27th, 2007, 05:15 PM posted to microsoft.public.access.reports
Chuck
external usenet poster
 
Posts: 261
Default Attn: Marshall Barton

On Thu, 27 Sep 2007 00:35:26 -0500, Marshall Barton
wrote:

Chuck wrote:

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.



I don't see how just adding some 0s can do the job. There
are really three separate portions to these insidious part
numbers, kind of like sorting on three fields. My idea is
to parse out the separate portions (left, middle and right)
and combine them into a canonical form with fixed length
portions that can be sorted normally.

E.g.
1F2 1D----0000002-------
D2A D-----0000002A-------
DJ2A DJ----0000002A-------
DJ3BB DJ----0000003BB------
. . .

If only the real part numbers were so easily parsed into the
three portions.


Marshall,

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.

Chuck
--
 




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 11:08 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.