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

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 http://www.access-programmers.co.uk/ubb/frown.gif

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

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)

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.

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

Nancy