Ordering an alphanumeric field

[ChristianC]

I have an alphanumeric field that I'd like to order, but Access does not recognize the numbers in the proper order. The field contains a work breakdown structure numbering scheme similar to what you'd see in a detailed job description. So, for example...

MG 1.1
MG 1.1.3
MG 1.10
MG 1.2

The problem is that when I try to sort, access will order in the following way:

MG 1.1
MG 1.10
MG 1.1.3
MG 1.2

How can I change this? Any help is appreciated!

[missinglinq]

Access does not recognize the numbers in the proper order.

is a false statement. Access does recognize numbers in the proper order, as well as letters and punctuation marks. The problem is that you're not asking Access to sort numbers, but rather alpha-numeric characters, as you indicated in the post title, which include letters, numbers and periods/decimal points! Access is doing exactly what you've asked it to do! When sorting an alpha-numeric field Access, like every other computer based system, sorts using the ASCII Table, where the period/decimal point comes before numeric characters.

Perhaps someone here knows of a work around.

[nico5038]

To use an alphanumeric sort on your data you would need to change it into:
MG 1.01
MG 1.01.03
MG 1.02
MG 1.10
etc.
So two positions with leading zero when < 10.
When the max number for the second level is e.g. 101, then you would need:
MG 1.001
MG 1.001.03
MG 1.002
MG 1.010
MG 1.101
When you want this easier you would need to use different fields like:

Code Level1 Level2 Level3
MG      1        1       0
MG      1        1       3
MG      1       10       1
 

etc.
Now you can use numbers for the Level# fields and get them sorted the way you expect.
Finally you could split the data (using a function or IIF() and MID() statements) and sort on that onstead of the complete field.

Nic;o)

[Killer42]

To use an alphanumeric sort on your data you would need to change it into:
MG 1.01
MG 1.01.03
...

There are probably other ways it could be done, but they may become rather messy. For example, to avoid changing your data, in a query you could...

• Create a calculated field which converts the format of the number to avoid changing your data. In other words, it could combine the first two characters and the numeric value of the rest of the field, formatted with leading zeroes.
• Create two calculated field. One would hold the "MG" part of the field. The other would hold the number part, as a number.

The fact that your "number" has more than one decimal point may complicate things. For one thing, the Val( ) function probably won't like it.

I rather like the idea of including each level in a separate field though, as nico suggested - holding information in a more structured way like that could provide numerous benefits further down the track, as well as simplifying this sort. To pluck one trivial example out of thin air, if you later change your mind about the format in which the data should be displayed, it would probably be easier. Or if you suddenly came up with a need to search by various levels within the number.

[missinglinq]

Indeed The fact that your "number" has more than one decimal point means that it isn't a number at all, and certainly will complicate things!

Good Luck!

[NeoPa]

The options already posted are good answers to what is quite a difficult concept.
Another answer which would suit some people better (depending on what they are most comfortable with) is to define a globally available function which takes in the string you currently use and re-formats it in such a way that it could be sorted in the way you would like - similar to Nico's layout.
Once that were created you would have the original string available for display and you could order by the returned result of the function.

[Killer42]

The options already posted are good answers to what is quite a difficult concept.
Another answer which would suit some people better (depending on what they are most comfortable with) is to define a globally available function which takes in the string you currently use and re-formats it in such a way that it could be sorted in the way you would like - similar to Nico's layout.
Once that were created you would have the original string available for display and you could order by the returned result of the function.

One thing to keep in mind though, using a function-derived value for sorting in this way could have big performance implications. If we're talking about relatively small amounts of data for the processing power available, then no matter. Otherwise, it might be better to use the function at time of record creation/update, and store the derived value in the record, then use this column in sorting.

[NeoPa]

This is true.
Storing the data differently would have to be an 'also' rather than an 'instead of' though.
Like the change back of the avatar Killer.

[Killer42]

This is true.
Storing the data differently would have to be an 'also' rather than an 'instead of' though.

Oh, definintely. The idea was to add a redundant copy of the field, for use as a sorting index.

Like the change back of the avatar Killer.

Thanks. I think the orange tunnel one is my favourite (so far).