The old problem of number ordering

[euphonium01]

I am putting together a database to log the wiring and connections on a particular vehicle. Each Connector has numerous PIN-OUTs each with an ID, the ID is fixed, I cannot change it. This brings with it the problem of the Connection Codes not being in order. I have a sample...

PIN-OUTs
BS2-5
BS2-6
BS6-1
BS6-10
BS6-11
BS6-12
BS6-2


BS6-3
The above is how it shows when Sorted A - Z Ascending

What I would like is...
BS2-5
BS2-6
BS6-1
BS6-2
BS6-3
BS6-10
BS6-11
BS6-12
Etc..

I know that numbers are stored as a Text Value and I can change reletively easy, but I am struggling when I add the Letters at the front. There are two Letters at the beginning for 97% of the records (1,327 of them), I can manually correct any that have more.

If this can be done, I intend to put a hidden ID Field with sequential numbering and Order By that Field instead of the PIN-OUT field, that should keep the correct ordering. The particular vehicle is 24/25 years old, so the data is not going to change, be added to, or records deleted.

If anyone can help I would be in your debt?
Paul

[Welshgasman]

Keep the (if any prefix) in a separate field.?
Easy to concatenate when you need to see it as above.

Then your numerics should sort correctly.

Easier to concatenate values, than try and split them as above, if you end up with BSB prefix?

[Micron]

I agree, as long as BR4 coming before BS# is ok with you. If not, you could also either
a) enter all records one by one and use an autonumber field then always sort on the AN field, or
b) use a sort order field. You would probably would have to manually enter the sort order values
In b, the result would look like (before a sort)

BS2-5	2
BS2-6	4
BS6-1	6
BS6-10	12
BS6-11	14
BS6-12	16
BS6-2	8
BS6-3	10

I'd use values that left some sort of space in between, according to what I expect going forward (e.g. what I showed, or 5, 10, 15...)

[CJ_London]

I know you said you cannot change it but if you have the opportunity then you could state numbers as
bs6-02
bs6-03
etc

[June7]

Consistency in structure is critical when parsing strings. If every value has hyphen, it would be fairly simple to split string to use each part for sort criteria.

Does every value have alpha prefix? You already said the number of alpha characters is not consistent. You show the before-hyphen part counting up to only 6 - could it actually go much higher? In either case, again faced with the issue of numbers subject to alpha sort and have to parse that segment into two parts for sorting.

Potentially require parsing into 3 parts for sort criteria.

Sometimes a custom VBA procedure can be useful to parse strings.

[euphonium01]

I agree, as long as BR4 coming before BS# is ok with you. If not, you could also either
a) enter all records one by one and use an autonumber field then always sort on the AN field, or
b) use a sort order field. You would probably would have to manually enter the sort order values
In b, the result would look like (before a sort)

BS2-5	2
BS2-6	4
BS6-1	6
BS6-10	12
BS6-11	14
BS6-12	16
BS6-2	8
BS6-3	10

I'd use values that left some sort of space in between, according to what I expect going forward (e.g. what I showed, or 5, 10, 15...)

I think I agree too. Split the Alpha & Numeric content, add a Sort field to the 'Sorted' Numeric content, then add the Alpha part back. Entering them one at a time would take a lot longer I think, there's a lot of records to sort. Thanks guys, you have confirmed what I thought I would have to do. I will come back once I get that ID Field split, and let you know how it went.
Paul

[June7]

I doubt modifying table and data is absolutely necessary. As noted, splitting on hyphen could be a simple expression in query.

Is this sort requirement important anywhere else than on a report?

[Micron]

sort on the AN field, or

No, that was a OR b, not both, and that was based on you saying you can't change it. If you can split as June7 is saying, that would be my approach.