By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

Ordering an alphanumeric field

P: 10
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!
Dec 26 '06 #1
Share this Question
Share on Google+
8 Replies


missinglinq
Expert 2.5K+
P: 3,532
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.
Dec 26 '06 #2

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. Code Level1 Level2 Level3
  2. MG      1        1       0
  3. MG      1        1       3
  4. MG      1       10       1
  5.  
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)
Dec 26 '06 #3

Expert 5K+
P: 8,434
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.
Dec 26 '06 #4

missinglinq
Expert 2.5K+
P: 3,532
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!
Dec 27 '06 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 30 '06 #6

Expert 5K+
P: 8,434
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.
Dec 31 '06 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 31 '06 #8

Expert 5K+
P: 8,434
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).
Dec 31 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.