Sorting

[Guest]

Data in a field named "Subject code" needs to be entered as text in the
format A1, A2, A3..... in sequence so that first entry gets number A1, the
next A2.... At the moment I have got 40 entries from A1 to A40. I need to
sort these out so that all retrieved records in any query are sorted from A1
to A40 or vice versa. When I want to accomplanish this, access sorts the
records out but in the sequence A1, A10, A11, A12... etc. It does not follow
A1, A2, A3....
Does anybody know whats the solution is?

Many thanks

 

[Guest]

Had you entered A1 as A01, A2 as A02 . . . it would have sorted as desired.
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?

 

[John Vinson]

Data in a field named "Subject code" needs to be entered as text in the
format A1, A2, A3..... in sequence so that first entry gets number A1, the
next A2.... At the moment I have got 40 entries from A1 to A40. I need to
sort these out so that all retrieved records in any query are sorted from A1
to A40 or vice versa. When I want to accomplanish this, access sorts the
records out but in the sequence A1, A10, A11, A12... etc. It does not follow
A1, A2, A3....
Does anybody know whats the solution is?

Many thanks

Well, it's sorting exactly as you're telling it to sort: text fields
sort alphabetically, and the character "1" comes before the character
"2".

Two suggestions:

- If the "A" is constant (and you'll not need B31 or K9) use a Number
field for the Subject Code, and set the Format of the field to "A"#.
This will store just the number, which will sort numerically, but
display it with a preceding letter A.

- Put in a calculated field by typing

Sortkey: Val(Mid([Subject code], 2))

and sort by it.

John W. Vinson[MVP]

 

[yeguo]

Data in a field named "Subject code" needs to be entered as text in the
format A1, A2, A3..... in sequence so that first entry gets number A1, the
next A2.... At the moment I have got 40 entries from A1 to A40. I need to
sort these out so that all retrieved records in any query are sorted from A1
to A40 or vice versa. When I want to accomplanish this, access sorts the
records out but in the sequence A1, A10, A11, A12... etc. It does not follow
A1, A2, A3....
Does anybody know whats the solution is?

Many thanks