Difficulty sorting an alphanumerical text field

[HCL]

I have a table containing details of parishioners who have died in the parish. The source for this were physical registers which were used to create table entries. Entry numbers existed in the registers and formed part of the details that were created in the "Deaths" table. These entry numbers identify a specific persons details and can be used to go to the actual register directly. In the majority of cases the entry numbers were sequential numbers which carried over across the registers. So they started at 1 and currently the latest number is 540 and these numbers have spanned some 5 physical registers. There have been a handful of numbers which were duplicated and these numbers were entered with a trailing alpha code. So in some cases the numbers were 101, 101A, 310, 310A, 399 399 A, etc.
Recently, an older register was found which also started at 1 and I was hoping to enter these details with an entry number starting with a leading zero, so that they appeared earlier in any listing; implying their age.
The entry number field is a text field to accommodate those numbers that have an alpha suffix.
I have created a query with a sorted calculated field, using the VAL function on the entry number, but cannot get the sort to produce these entry numbers in the correct sequence. So for example it gives me 01, 1, 2, 02, 3, 4 ,5, 6 etc (I have only created a couple of test entries with the leading zero to see if the sort works correctly). See images below.
I apologize if this question has been asked before and would appreciate any advice from this forum. Thanks.

 

[theDBguy]

Just thinking out loud; but instead of leading zeroes, perhaps you could use decimal numbers below 1.

eg. .01, .02, etc

 

[HCL]

Just thinking out loud; but instead of leading zeroes, perhaps you could use decimal numbers below 1.

eg. .01, .02, etc

Is there a problem with the VAL function dealing with leading zeros?

 

[Gasman]

Is there a problem with the VAL function dealing with leading zeros?

Not that I can see?

? val("10")
10
? val("010")
10
? val("10A")
10

However as they are all the same, how can you differentiate which should come first?
You can see that from your picture.

You *happen* to have 01 before 1, but 2 before 02 ?
Perhaps also sort by Len() ?

 

[HCL]

Not that I can see?

? val("10")
10
? val("010")
10
? val("10A")
10

However as they are all the same, how can you differentiate which should come first?
You can see that from your picture.

You *happen* to have 01 before 1, but 2 before 02 ?
Perhaps also sort by Len() ?

Sorry, but in my ignorance I was hoping that entering a leading zero to the new numbers 01,02,03 etc, the VAL function would sort them first before any of the existing numbers, so that the list would produce 01,02,03 then 1,2 3. But looking at your example of ?val("010") giving 10 implies that the leading zero is being dropped.

 

[Gasman]

So perhaps sort by Len() DESC and then your Val() field?
However then if you have 011 that is going to be at the top.

@theDBguy's suggestion is a good workaround, using a decimal point instead of leading zero.

 

[HCL]

So perhaps sort by Len() DESC and then your Val() field?
However then if you have 011 that is going to be at the top.

@theDBguy's suggestion is a good workaround, using a decimal point instead of leading zero.

I appreciate the various comments so will go back to the drawing board. Thanks.

 

[RonPaii]

I have a table containing details of parishioners who have died in the parish. The source for this were physical registers which were used to create table entries. Entry numbers existed in the registers and formed part of the details that were created in the "Deaths" table. These entry numbers identify a specific persons details and can be used to go to the actual register directly. In the majority of cases the entry numbers were sequential numbers which carried over across the registers. So they started at 1 and currently the latest number is 540 and these numbers have spanned some 5 physical registers. There have been a handful of numbers which were duplicated and these numbers were entered with a trailing alpha code. So in some cases the numbers were 101, 101A, 310, 310A, 399 399 A, etc.
Recently, an older register was found which also started at 1 and I was hoping to enter these details with an entry number starting with a leading zero, so that they appeared earlier in any listing; implying their age.
The entry number field is a text field to accommodate those numbers that have an alpha suffix.
I have created a query with a sorted calculated field, using the VAL function on the entry number, but cannot get the sort to produce these entry numbers in the correct sequence. So for example it gives me 01, 1, 2, 02, 3, 4 ,5, 6 etc (I have only created a couple of test entries with the leading zero to see if the sort works correctly). See images below.
I apologize if this question has been asked before and would appreciate any advice from this forum. Thanks.

If you sort the field directly alphabetically i.e. without the val function your sort will come out as follows.

01, 02, 1, 2, 3, 4, 6

but if you add something like 10 then you will get

01, 02, 1, 10, 2, 3, 4, 6

if you are looking for something like

01, 1, 02, 2, 3, 4, 6

You will need to look at a custom function or add a column for to ID the difference.

 

[CJ_London]

really need to establish all the variations but you can combine format and val functions
?format(val("01D"),"000")
001
?format(val("12"),"000")
012
?format("310","000")
310

You can extend that to include the last character if there is one
?format(val("31D"),"000") & iif(not isnumeric(right("31D",1)),right("31D",1),"")
031D
?format(val("310"),"000") & iif(not isnumeric(right("310",1)),right("310",1),"")
310
?format(val("2C"),"000") & iif(not isnumeric(right("2C",1)),right("2C",1),"")
002C

Sort on this calculated column, display if you want to, otherwise display the actual value

 

[Mark_]

I have a table containing details of parishioners who have died in the parish. The source for this were physical registers which were used to create table entries. Entry numbers existed in the registers and formed part of the details that were created in the "Deaths" table. These entry numbers identify a specific persons details and can be used to go to the actual register directly. In the majority of cases the entry numbers were sequential numbers which carried over across the registers. So they started at 1 and currently the latest number is 540 and these numbers have spanned some 5 physical registers. There have been a handful of numbers which were duplicated and these numbers were entered with a trailing alpha code. So in some cases the numbers were 101, 101A, 310, 310A, 399 399 A, etc.
Recently, an older register was found which also started at 1 and I was hoping to enter these details with an entry number starting with a leading zero, so that they appeared earlier in any listing; implying their age.
The entry number field is a text field to accommodate those numbers that have an alpha suffix.
I have created a query with a sorted calculated field, using the VAL function on the entry number, but cannot get the sort to produce these entry numbers in the correct sequence. So for example it gives me 01, 1, 2, 02, 3, 4 ,5, 6 etc (I have only created a couple of test entries with the leading zero to see if the sort works correctly). See images below.
I apologize if this question has been asked before and would appreciate any advice from this forum. Thanks.

Were this me, I would store not only the registry entry, but which register they were in. This removes the need for trailing characters, prepended numbers, or any other change to data that is solved simply by saving as Register and Registry number. This will also help if you have other issues where a register does not continue a sequence you would otherwise expect.

 

[HCL]

really need to establish all the variations but you can combine format and val functions
?format(val("01D"),"000")
001
?format(val("12"),"000")
012
?format("310","000")
310

You can extend that to include the last character if there is one
?format(val("31D"),"000") & iif(not isnumeric(right("31D",1)),right("31D",1),"")
031D
?format(val("310"),"000") & iif(not isnumeric(right("310",1)),right("310",1),"")
310
?format(val("2C"),"000") & iif(not isnumeric(right("2C",1)),right("2C",1),"")
002C

Sort on this calculated column, display if you want to, otherwise display the actual value

Again, many thanks for all the suggestions. Clearly my solution is going to be the inclusion of many functions.

 

[RonPaii]

Again, many thanks for all the suggestions. Clearly my solution is going to be the inclusion of many functions.

Instead of a bunch of functions and the slows they will cause your queries, consider normalizing your data so you can easily control the sort and handle future changes.

 

[The_Doc_Man]

The simplest solution that is also theoretically correct is to identify each register with a number and then record both register number and entry number. There is no problem in sorting by multiple fields. If you need to later show which register to consult as well as which entry, you have it in your database. If the register and entry numbers are actually always numeric, then sorting will be ORDER BY RegNum, EntNum or something like that.

 

[bbfromgb]

Phillip Stiefel published an excellent paper about numerals sorting

Sorry, I'm not allowed to publish links yet

 

[sonic8]

Phillip Stiefel published an excellent paper about numerals sorting

Thank you.
Here is the link:

Logical numerals sorting in Access with VBA

Sort numerals in your Access database text fields by their logical, numerical order. Working sample code and in-depth explanation.

codekabinett.com

 

[arnelgp]

created a user-defined function in module1 and use it in query1 (Exrp1) and Sort on this calculated column.
see query1.

 

[GustavBrock]

I have a table containing details of parishioners who have died in the parish. The source for this were physical registers which were used to create table entries. Entry numbers existed in the registers and formed part of the details that were created in the "Deaths" table. These entry numbers identify a specific persons details and can be used to go to the actual register directly. In the majority of cases the entry numbers were sequential numbers which carried over across the registers. So they started at 1 and currently the latest number is 540 and these numbers have spanned some 5 physical registers. There have been a handful of numbers which were duplicated and these numbers were entered with a trailing alpha code. So in some cases the numbers were 101, 101A, 310, 310A, 399 399 A, etc.
Recently, an older register was found which also started at 1 and I was hoping to enter these details with an entry number starting with a leading zero, so that they appeared earlier in any listing; implying their age.
The entry number field is a text field to accommodate those numbers that have an alpha suffix.
I have created a query with a sorted calculated field, using the VAL function on the entry number, but cannot get the sort to produce these entry numbers in the correct sequence. So for example it gives me 01, 1, 2, 02, 3, 4 ,5, 6 etc (I have only created a couple of test entries with the leading zero to see if the sort works correctly). See images below.
I apologize if this question has been asked before and would appreciate any advice from this forum. Thanks.

This will do:
SELECT Entry.Entry_N
FROM Entry
ORDER BY Val ([Entry_N]), Entry.[Entry_N]

 

[sonic8]

The source for this were physical registers which were used to create table entries.

I have some experience with municipal registers, even though rather with land registers, and I strongly want to second, or third, @MarkK and the @The_Doc_Man 's suggestions of also storing the identity of the register itself with the record. This is the proper way to reference such register items.

 

[arnelgp]

result based on what you want to accomplish in post #5:
Expr1 (sort order) is being shown here, but you can hide it on the Query design.