Sorting an alpha-numeric field (1 Viewer)

Nancy26 · Dec 10, 2001

I have a field in a query containing alpha-numeric data. I have an "order by field" but I am not getting the results I need.

This list represents the way the order by currently works.

3001
3001N
300-4P
3005
300-5B
3005C

This is how they should sort:
300-4P
300-5B
3001
3001N
3005
3005C

Is this possible?

Thanks,

Nancy

pcs · Dec 11, 2001

from help:
Numbers stored in Text fields are sorted as strings of characters, not numeric values. Therefore, to sort them in numeric order, all text strings must be the same length with shorter numbers padded with leading zeros. For example, the result of an ascending sort of the text strings "1", "2", "11", and "22" will be "1", "11", "2", "22". You must pad the single-digit numbers with a leading zero for the strings to be sorted properly: "01", "02", "11", "22".

probably not the news you want to hear

you might be able, however to create an expression in your query to extract the 'true' number part and the remainder and use these 2 fields for the sort order. based on your sample data, looks like you might be all over the place with variations. so the extraction process might be a real bear...
it might be easier to pad them to a fixed length as per the help file.

hth,
al

Jack Cowley · Dec 11, 2001

I agree. I have pondered your dilemma and have come to the same conclusion as Al. You can do it but it will require some fiddling to get you there.

(This endorsement was paid for by Al)

KKilfoil · Dec 11, 2001

If all of your record values START with the number you are trying to sort by (as in your examples), you can use the Val() function in a query to extract the leading number. Ensure this field is listed first in your query, and is sorted in ascending order. Make your original field sorted as well, to break the 'ties' from this new field, as Val("300-4P') and Val("300-5B") both return the value 300.

Nancy26 · Dec 11, 2001

Thanks for all the replys. It seems like it will be a major pain!

Nancy

namliam · Sep 13, 2013

Intresting thing here is that the sort seems to be skipping the "-" part of it, I would expect the two 300- ones indeed to be on top of i you sort your query...

I would make a hidden field: Replace([Field1],"-","0",1) AS SortField
and sort by that... problems might occure if you have other characters causing a simular issue

Brianwarnock · Sep 13, 2013

WOW! This is a 12 year old thread.
If anybody is interested in alphanumeric sorting here are two threads that they might like to read

http://www.access-programmers.co.uk/forums/showthread.php?t=241599&highlight=Alpha-numeric+sort

And best of all

http://www.access-programmers.co.uk/forums/showthread.php?t=120275

Brian

namliam · Sep 13, 2013

LOL yeah 12 years, dont know how this showed up in my "to be answered list"

Brianwarnock · Sep 13, 2013

Somebody replied before you which probably prompted your response, but they have now deleted their post.

Brian

Brianwarnock · Sep 13, 2013

Thanks for clearing that up, glad Namliam and I aren't going daft in our old age.

Brian

spikepl · Sep 13, 2013

@terrysoper

FYI you have already been reported as spammer by at least two different people - stop posting like an idiot or you will be chucked the moment one of the admins log on

pr2-eugin · Sep 13, 2013

@terrysoper : The forum's rules - 10 Post Threshold for Profile Fields, Signatures, Avatars, pictures, etc.

Look at the thread. A Mod will have a word with you soon.
http://access-programmers.co.uk/forums/showthread.php?t=223479

spikepl · Sep 13, 2013

Next time RTBM : READ THE BLOODY MANUAL! AT the top of each forum there is a STICKY POST with instruction how to post pictures before 10 posts!

Brianwarnock · Sep 13, 2013

Is he too stupid to realise how his penchant for deleting posts causes threads to appear disjointed and others posts embarrassing nonsense.

Brian

pr2-eugin · Sep 13, 2013

Brianwarnock said:
Is he too stupid to realise how his penchant for deleting posts causes threads to appear disjointed and others posts embarrassing nonsense.

Brian

Brian, I think one of the Mod's Banned the spammer and deleted all their post.

Brianwarnock · Sep 13, 2013

Yes since posting I have discovered that he has totally vanished, we do seem to be suffering from these people at the moment. When I first came on here a decade ago it was very rare, in fact I don't recall it at all. It would annoy the hell out of me if I was as active as you.

Brian

pr2-eugin · Sep 13, 2013

Brianwarnock said:
It would annoy the hell out of me if I was as active as you.

Brian

Hence my cry for stopping this..

Sorting an alpha-numeric field (1 Viewer)

Nancy26

New member

pcs

Registered User.

Jack Cowley

Registered User.

KKilfoil

Registered User.

Nancy26

New member

namliam

The Mailman - AWF VIP

Brianwarnock

Retired

namliam

The Mailman - AWF VIP

Brianwarnock

Retired

Brianwarnock

Retired

spikepl

Eledittingent Beliped

pr2-eugin

Super Moderator

spikepl

Eledittingent Beliped

Brianwarnock

Retired

pr2-eugin

Super Moderator

Brianwarnock

Retired

pr2-eugin

Super Moderator

Similar threads

Users who are viewing this thread