Call Number Sorting

[ifgd]

Can anyone tell me why Access is sorting these call numbers this way?

As you'll notice, it sorts fine up to the third digit at the item level. For example,

1029.1
1029.2
1029.3
1029.4
1029.5
1029.6
1029.7
1029.8


1029.9
103.1
103.2
.
.
.
103.9
1030.1
1030.2
1030.3
etc.

Why is the sorting limited to those first three digits? Is there a way to fix this?

[June7]

It's alpha sort rules, not numeric, this means one character at a time is compared. 2 comes before 3, therefore 1029.9 sorts before 103.1.

Would need placeholder zeros.

0103.02
0103.14
1029.09
1030.01

Or do you want:

102.01
102.02
1029.01
...
103.01
103.02
103.14
1030.01

[ifgd]

Is there a way to fix this without adding placeholder zeros?

[June7]

Maybe parse the [N Number] parts to separate fields so you have numeric values to sort with. There's a lot of parts. How consistent is the structure?

Recommend not using spaces nor punctuation/special characters (underscore only exception) in naming convention.

Part1: Left([NNum], 3)
Part2: Val(Mid([NNum], 5))
Part3: Val(Mid([NNum], 8))
Part4: Val(Mid([NNum], 10))
Part5: Val(Right([NNum], Len([NNum]) - InStrRev([NNum], "_")))

If any variation in the number of characters of parts 1 - 3 or field is empty, this gets more complicated.

[ifgd]

Maybe parse the [N Number] parts to separate fields so you have numeric values to sort with. There's a lot of parts. How consistent is the structure?

Recommend not using spaces nor punctuation/special characters (underscore only exception) in naming convention.

Part1: Left([NNum], 3)
Part2: Val(Mid([NNum], 5))
Part3: Val(Mid([NNum], 8))
Part4: Val(Mid([NNum], 10))
Part5: Val(Right([NNum], Len([NNum]) - InStrRev([NNum], "_")))

If any variation in the number of characters of parts 1 - 3 or field is empty, this gets more complicated.

You're a little over my head on this.

Here's the situation. The entries are call numbers, in this case for example, PhC_23_1_1000_1.

So, PhC_23 is Photo Collection number 23. The next "1" is constant. The number after that, "1000", is the folder number. This is the important number. This is the one that needs to be in numerical order. The last number is items within the folder.

The first three parts of the call number never change. It's the last two that are giving me problems.

Thanks so much for your help on this.

[June7]

What exactly is over your head - the functions I used in the expressions or where to use the expressions? They would be used in a query to calculate fields.

NNum is my suggestion for a fieldname that does not use spaces. If you don't want to change your naming structure then use your [N Number] instead.

Can reduce to 3 calculations if you want.

CollNum: Left([NNum], 8)

FolderNum: Val(Mid([NNum], 10))

SeqNum: Val(Right([NNum], Len([NNum]) - InStrRev([NNum], "_")))

If you need to better understand the functions, search Access Help or the web.

[ifgd]

Looks like I'll need to dive into youtube and this website to better understand this. Thanks so much for getting me started! I did figure out how to make it sort correctly through convoluted copy and pasting between access and excel and an excel formula after subtracting the consistent portions of the call number.

In any case, thanks again.

[June7]

Creating field in query with expression is basic Access functionality.

SELECT Left([NNum], 8) AS CollNum, Val(Mid([NNum], 10)) AS FolderNum, Val(Right([NNum], Len([NNum]) - InStrRev([NNum], "_"))) AS SeqNum FROM yourtable ORDER BY Val(Mid([NNum], 10)), Val(Right([NNum], Len([NNum]) - InStrRev([NNum], "_")));

or

SELECT * FROM yourtable ORDER BY Val(Mid([NNum], 10)), Val(Right([NNum], Len([NNum]) - InStrRev([NNum], "_")));