sorting alphabetically & numerically

[jknox]

I am trying to figure out how to sort a column in access the same way it comes out in excel.
I have a text list of "buildings" ie: 89, 33, 270C, 270B, 270A etc. In excel this simply sorts alphanumerically ie 33, 89, 270A, 270B, 270C.

I made the mistake of changing the format from text to number, lost all the data and successfully scared myself into not wanting to experiment anymore so any input I could get would be very appreciated

 

[rainman89]

Is this in a report?

 

[jknox]

at the moment I have a table and a bunch of queries but if I can find a way to sort these I'll build whatever I need to

 

[RuralGuy]

I believe you can create another field in your query:
SortField:=Val([buildings])
...and it will strip the alpha and give you a number on which you can sort.

 

[jknox]

that sounds like a step closer but I also need all of the 270a, 270b, 270c, 270d and 270e buildings sorted together

 

[RuralGuy]

You may need to create two sorting fields and pull out the alpha into the second field. Is it always just 1 alpha character at the end of a number?

 

[RuralGuy]

Actually try sorting on the numeric field first and then the original text field second. I think that will give you the order you want. No need for an additional alpha field.

 

[The_Doc_Man]

While I would never call anyone a liar, Excel doesn't sort that way unless something else is going on.

The sequence you named....

33, 89, 270A, 270B, etc. etc.

SHOULD sort as

270A
270B
33
89

if Excel is sorting as a text field. So what you have going on is an improper understanding of the EXCEL sort. When you import data from a file, Excel attempts to understand the format of the colums. If the first several columns APPEAR to be numeric, Excel will treat it that way. However, the mixed alphanumeric string you have demonstrated should NEVER sort the way you described if it were treated consistently with the data type.

To make it sort correctly, you need to set up a fixed-column sort by RIGHT-justifying everything. You also need to test whether the field could be numeric (see Access function IsNumeric() in the help files) in order to tack on a trailing blank in order to align everything including those fields with trailing alphabetics. In essence, if the field cannot be numeric, left justify it. If it cannot be numeric, left justify it, then "steal" a leading blank and move it to become a trailing blank.

 

[jknox]

That seemed to do it although if there was a way to change multiple cells at the same time it would be easier.
IE in excel I can select multiple cells, type what I want and hit ctrl enter and it changes them all.

I wouldn't be surprised if there was more to the excel sorts then I know, I am teaching myself both programs in order to increase my personal efficiency