What characters sort below Z in when sorting a singlc character col in ascending order

I thought finding the answer to the above would be easy, but so far no luck.

I need a printable character that will sort below z or Z when sorting a single col in normal sort order.

Thanks

Elton
katzwhiteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

walterecookCommented:
Not real sure but what you can do is create a second column in the query:
newcolumn: asc([yourFirstColumn])
and sort on it
Here a number of characters sort after z:
~
would work

Walt
0
NestorioCommented:
Look at this table and choose a character below the "z". For instance "{".

http://www.asciitable.com/
0
Steve BinkCommented:
For a list of all the ASCII codes, try this little sub:

Public Sub ShowAscii()
Dim x

For x = 0 to 252 Step 4
    Debug.Print x & ": " & Chr(x), x+1 & ": " & Chr(x+1), x+2 & ": " & Chr(x+2), x+3 & ": " & Chr(x+3)
Next

End Sub

Also, remember that each available code page has its own sort order, and you can change the default sort order in specific implementations (like InStr).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GRayLCommented:
Depends what you mean by below.  Z is asc 90, z is asc 122. A space, asc 32 is below 90 or 122, as are all the number digits and all the upper and lower case letters, and most punctuation marks.
0
katzwhiteAuthor Commented:
I have a table with a 1 char text col with some codes which as an example are:

B,C,N,3,7,~, Z and %

When I sort on this column in ascending order the rows display as follows from top to bottom on the screen:
%,~,3,7,B,C,N,Z

I would like to find some char to replace the %, ~  that would sort below the Z.

thanks for the asistance

Elton
0
Steve BinkCommented:
In that case, simply look at the ASCII table (link provided by Nestorio, sub provided by me) and pick a character you like.  Here's some sample for you:

88: X         89: Y         90: Z         91: [
92: \         93: ]         94: ^         95: _
96: `         97: a         98: b         99: c
100: d        101: e        102: f        103: g
104: h        105: i        106: j        107: k
108: l        109: m        110: n        111: o
112: p        113: q        114: r        115: s
116: t        117: u        118: v        119: w
120: x        121: y        122: z        123: {
124: |        125: }        126: ~        127: 
128: €        129:         130: ‚        131: ƒ
0
GRayLCommented:
Look at you Windows Character Map.  left curly bracket, vertical bar, right curly bracket, {|} will do the trick.
0
Steve BinkCommented:
Looking at that ASCII list, I just noticed tilde is 126, while z is 122.  Shouldn't tilde come after 'z' in sort, then?  
0
katzwhiteAuthor Commented:
So what am I doing wrong?

When I substiute any of the above characters that I can find on my keyboard and select the col, right click and sort ascending, the Z is still at the bottom of the list.

Elton
0
Steve BinkCommented:
I just created a table, put in some one-character sample data, then sorted.  Here's the list I got:

Field1
!
(
)
*
@
[
]
{
}
~
0
1
2
3
8
9
a
A
B
b
C
c
X
x
Y
y
Z
z

And the race is on...lol.  How to change the sort order from alphabetical to numeric.  No, the options does not have it...it only has the 'default database sort order' for language-based sorting (like Chinese, Hebrew, etc.)
0
walterecookCommented:
Or you could just look at the table via a query as I suggested a while back.

Walt
0
walterecookCommented:
Similarly you can do an advanced sort.
Records|Filter|advanced
Create a new field with the expression
asc([yourField])
Sort on it
Click Apply Filter.

Walt
0
katzwhiteAuthor Commented:
It looks like it will be complicated to do, as the advanced sort will move the numbers below the alpha characters.

I appreciate the assistance.

I'll try to divide the points as each of you have provided interesting ideas and contributions.

Thanks

Elton
0
GRayLCommented:
Is this because Access used the 16 bit unicode character set to define its character strings?  I saw something about that in Help a short time ago but darned if I can find it now!
0
Steve BinkCommented:
It has to do with the code page Access is using on the particular system.  In MSSQL, you can change the default sorting code page to be whatever you want.  Access provides for the common foreign languages, but you don't get alot of versatility with it.

I think Walt's solution is your answer.  It's an easy suggestion to implement (just adding a column to the query for sorting purposes), and will give you results in the same order as the ASCII representation.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.