Sorting the Output of the Query in a different way.. 1

[JohnSmith]

How can I sort the output of the query in the following manner?


A
a
B
b
C
c
1
2

etc.

I want all uppercase A and a and the uppercase B and b etc.

Any help would be greatly appreciated.

Thanks in advance

 

[kathryn]

Unfortunately, all I can think of is for you to create a sorting table, call it tblSort, with the sort order you want and a sort number, then link tblSort with your data and sort the query on the sort number.

I can give you details if you let me know what your table name is and the name of the field on which you want to sort. I am assuming that your data is not really A, a, B, etc, but more like Adam, ant, Betty, bee, Carl, etc.

 

[JohnSmith]

Kathryn,

You are right - data is in the form of Adam, ant, bee, Carl etc.

I would appreciate further details. Thanks a million for your help.

 

[MichaelRed]

JohnSmith & Kathryn,

Look at the "Option Compare" statement. Option Compare Binary should do what you want. Unfortunately it may have side effects on other aspects of your process which are not desireable. I have not used this in a while, but it will provide the sort order noted.

It should be possible to isolate the effect on the sort order by call to the individual query (or queries) whiere this effect is desired in a seperate module.

Another possibility would be to create - in the query - a new field which is the ASC of the first character, and use this as the sort order field.

MichaelRed
There is never time to do it right but there is always time to do it over