Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-11-04
15
Medium Priority
?
3,082 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:katzwhite
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 17

Assisted Solution

by:walterecook
walterecook earned 600 total points
ID: 12496347
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 12496872
Look at this table and choose a character below the "z". For instance "{".

http://www.asciitable.com/
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12497377
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:GRayL
ID: 12499063
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
 

Author Comment

by:katzwhite
ID: 12499185
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12499281
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
 
LVL 44

Expert Comment

by:GRayL
ID: 12499295
Look at you Windows Character Map.  left curly bracket, vertical bar, right curly bracket, {|} will do the trick.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12499331
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
 

Author Comment

by:katzwhite
ID: 12499335
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
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 600 total points
ID: 12499385
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
 
LVL 17

Expert Comment

by:walterecook
ID: 12499414
Or you could just look at the table via a query as I suggested a while back.

Walt
0
 
LVL 17

Assisted Solution

by:walterecook
walterecook earned 600 total points
ID: 12499442
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
 

Author Comment

by:katzwhite
ID: 12499609
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 300 total points
ID: 12499631
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 600 total points
ID: 12505232
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question