Sorting Obscur Numbering Systems

Hi All,

Can anyone shed any light on this problem, I have a table with Lab Numbers and Part Numbers. I cannot change the numbering system.

LAB NUM PART NUM

12.11.10a 41.4.20
12.11.9a 41.4.2
12.8.1a 10.10
12.8.1a 10.9

I guess you can see the problem, I need to be able to sort as follows:

LAB NUM PART NUM

12.11.9a 41.4.20
12.11.10a 41.4.20
12.8.1a 10.9
12.8.1a 10.10

Any suggestions greatly apreciated!?

 

[willir]

Ah yes, another "null" post. I hope the Tek-Tip guys can resolve their upgrade problems -- I feel for them!

I cannot change the numbering system.

You can not change the numbering system, but you can create a sort field or fields.

Something like...
[tt]
LAB NUM PART NUM SortedPartNo

12.11.10a 41.4.20 12.11.10a.41.04.20
12.11.9a 41.4.2 12.11.09a.41.04.02
12.8.1a 10.10 12.08.01a.10.10.00
12.8.1a 10.9 12.08.01a.10.09.00
[/tt]

Access / JetEngine will use a alphabetical sort since you have numbers + letters + special characters.

A number sort...
1
2
3
...
10
11
...
100
101
etc.

An alphabetical sort
9
8
7
...
3
2
1
10
100
101

To emmulate a number sort, 1 2 3 4 ... 99 100, etc., you have to add leading zeroes ("0's") to the number. If your numbers are going to have 3 digits, each number must have 3 characters...

001
002
...
010
...
100

With alpha + numerics, same thing...

01a
02a
...
10z

What you do not want is...
01a
02a
...
100z

In this case, you have to add one more leading zero...
001a
002a
...
100z

The use of decimal kind of complicates things, but not that much. Notice
- the entire part number is the same for each record - a must
- each group of numbers between the decimals is the same length; also a must.
- any missing data will still have the leading zeros, ie, "00"

SortedPartNo
12.11.10a.41.04.20
12.11.09a.41.04.02
12.08.01a.10.09.00

The sort field can be rebuilt any time based on the original part number.

Probably best to use a Function for this.

Richard

 

[SkipVought]

hi (),

Just to add a note to willir's excellent post.

BIG difference sorting NUMBERS and NUMERIC STRINGS.

Numbers sort
1
2
10
20

Numeric Strings sort
1
10
2
20

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[willir]

Thanks skip -- I messed up and gave a reverse sort example. I guess it does not help being interrupted 20 times during the post.

 

[SkipVought]

inter
.....
upted? Say WUT? ;-)



Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}