Sorting text "properly"

[A441OTA]

Good afternoon and TGIF!!

I have a database program that supports a survey process. I need to be able to sort questions by question number and subitem, for instance 1a, 1b, 1 c, etc.

I have the question number in a field and the subitem in another
Qst_Number, Qst_Subitem

This works great until they go over 26 subitems and start using "aa", "bb", "cc", and so on.

When I sort the records by Qst_Number, Qst_Subitem I get
a
aa
b
bb
c
cc
etc.


I need a through z, then aa through zz

I use the sort when I send the data to Word for reports. Using an ADO recordset of data pulled from an Access Table.

"SELECT * FROM SurveyQuestionnaire_SurveyID ORDER BY Qst_Number, Qst_Subitem"

Any suggestions??

Thanks,
Mary

[brucevde]

Try this statement

strSQL = "SELECT * FROM SurveyQuestionnaire_SurveyID 
ORDER BY Qst_Number, Format(Qst_Subitem,'@@')"

[A441OTA]

God Bless you Bruce!!!!

Thanks so much that did the trick!!!! It also worked in the data entry screens as well when I pulled up the recordsets then!!!

Have a great holiday!

[RobDog888]

Brucevde, why would "z " be sorted before "aa", resulting in "a " - "z "
and then "aa" - "zz"? I understant that the @@ places a space
from right to left when there is not enough characters to format
the string. Shouldn't it go "a " and then "aa"?

[brucevde]

As you mentioned, format fills Right to Left and the @ symbol reserves a space. Formatting a single letter results in " a" and not "a ". Since the space character comes first in the sort order we get the desired affect of

" a"
" b"
" z"
"aa"
"ab"
"az"
"ba"
"bz"
"zz"

[RobDog888]

Ok, got it now.

Thanks.