Sorting an alpha-numeric field

[Nancy26]

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]

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]

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]

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]

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

Nancy

 

[namliam]

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]

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]

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

 

[Brianwarnock]

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

Brian

 

[Brianwarnock]

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

Brian

 

[spikepl]

@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]

@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]

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]

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]

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]

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]

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

Brian

Hence my cry for stopping this..