Can I sort a table in ASCII code order rather than alphanum (natural) order? (1 Viewer)

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
I have a table and often sort it on the NAME field. The table includes records with Unknown and Erroneous names that I used to put square brackets around to keep them separate, but I would like all of these to be at the end of an ascending order. I don't want to insert ZZ prefixes to implement this, since I would need to strip off such prefixes in reports. Is there a preference setting or other simply way to get ASCII code order? If so I could use curly brackets or tilda prefixes to have such names follow Z, e.g., {Unknown} or ~Doe, John is a fake entry.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:06
Joined
Feb 19, 2002
Messages
36,365
The standard order is ASCII so I'm not sure what you want.
 

Isaac

Lifelong Learner
Local time
Today, 09:06
Joined
Mar 14, 2017
Messages
6,558
Sure you can do that. Just create a column that returns the code and then sort on that column. What are you having trouble with?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:06
Joined
Sep 21, 2011
Messages
10,574
If you can already identify those records, why not add a field that identifies as such, then sort by that first?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
6,371
Code:
SELECT IIf(Left([contactName],1)="[",1,0) AS Sort1, tblContacts.ContactName
FROM tblContacts
ORDER BY IIf(Left([contactName],1)="[",1,0), tblContacts.ContactName;
Query2 Query2

Sort1ContactName
0​
Christa Neuhuber
0​
Erna Gruber
0​
Hans Huber
0​
Milly Miller
1​
[Aaron Rodgers]
1​
[Abe Lincoln]
1​
[Mike Brown}
 

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
It does not sort in ASCII order! At least character strings are not sorted that way. I pasted a screen clip of my table that is sorted showing that "{" and "~" prefixed strings rise to the top, not the end, of my ascending order. I know there are solutions that involve additional fields as well as things I can do in queries and reports, but I have my reasons for wanting to do this in the table view without adding fields and queries. From the responses, it sounds like there is no simple preferences setting to get lexicographic order based on ASCII codes when sorting a table. Correct?
clip.jpg
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:06
Joined
Feb 28, 2001
Messages
22,832
The database IS sorting in an appropriate order. By making the brackets part of the data in the field, you make them eligible for use in sorting as well. You can request either Option Compare Text (for which the comparisons are case-insensitive) or Option Compare Binary (which uses the ASCII values of each letter.) As it happens, Binary is the default option. So you ARE sorting in the correct order, but you have introduced extraneous characters that are ALSO eligible for the sort.

If you want to show something special, do it in another column. Anything you put in the column that is the basis for sorting WILL be sorted according to raw ASCII.
 

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
The standard order is ASCII so I'm not sure what you want.
That does not appear to be the case. ASCII codes for {, }, | and ~ are higher than all alphanumeric and other special characters, therefore strings that begin with those characters should be at the end of an ascending sort. However, all strings that begin with any special character rise to the top of an ascending order sort (including these 4 characters).
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:06
Joined
Sep 21, 2011
Messages
10,574
TBH, I know the OP said no extra fields, but I just added a field of ASC(Left([Description],1)) and the shows the first character ASCII value
However if I try and sort by that field, I get 'Data type mismatch in criteria expression' ?

All the query is, is
Code:
SELECT TestTransactions.ID, TestTransactions.Description, Asc(Left([description],1)) AS Expr1
FROM TestTransactions
ORDER BY (Asc(Left([description],1)));
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:06
Joined
Feb 28, 2001
Messages
22,832
After searching the web for this problem, it appears in multiple environments, in each case slightly differently. Specifically to Access, are you using the extended character set option (i.e. UTF-8) or regular ASCII character sets? The presence of non-ASCII characters almost always screws up sort order in some way for Access SQL and SQL Server as well as a couple of other cases I noted. The consensus of the articles I found with the general search is that if you cannot make a standard sort do what you want, you will HAVE to put in another field to hold something to precisely define the sort order OR you will have to learn to live with the sort order you get. There is no magic adjustment to make Access follow a specific non-standard sort presentation that doesn't involve SOME type of accommodation on your part.
 

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
The database IS sorting in an appropriate order. By making the brackets part of the data in the field, you make them eligible for use in sorting as well. You can request either Option Compare Text (for which the comparisons are case-insensitive) or Option Compare Binary (which uses the ASCII values of each letter.) As it happens, Binary is the default option. So you ARE sorting in the correct order, but you have introduced extraneous characters that are ALSO eligible for the sort.

If you want to show something special, do it in another column. Anything you put in the column that is the basis for sorting WILL be sorted according to raw ASCII
This sounds promising. Where do I find the Option for Compare Binary so that I can switch between these options to see how they work. I couldn't find it in Options (I am using MS 365 and the title bar for the DB includes the phrase "Access 2007 - 2016 file format").

I was confused by your statement that the brackets that I put into the data made them eligible for sorting as well. I see that to be the case in that they are grouped together, however curly brackets should be sorted after my Z-names, not before my A-names.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:06
Joined
Feb 28, 2001
Messages
22,832
Since I don't know your field settings and character-set choices, I can't tell you why anything sorts in any particular way. All I can tell you is that like it or not, SOMETHING is leading to that sort order. Let me ask one more question. Since it appears that you are using a proportional font, is it possible that a leading space exists (to the immediate left of a "{" character)? Because of kerning, a leading space before a special character would not be very big because kerning DOES depend on the next character. If you have a leading space there to the left of the special characters, then they would appear in the presented order. WHY you would have a space there, I cannot know. But it would go a long way towards explaining the presented order.
 

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
After searching the web for this problem, it appears in multiple environments, in each case slightly differently. Specifically to Access, are you using the extended character set option (i.e. UTF-8) or regular ASCII character sets? The presence of non-ASCII characters almost always screws up sort order in some way for Access SQL and SQL Server as well as a couple of other cases I noted. The consensus of the articles I found with the general search is that if you cannot make a standard sort do what you want, you will HAVE to put in another field to hold something to precisely define the sort order OR you will have to learn to live with the sort order you get. There is no magic adjustment to make Access follow a specific non-standard sort presentation that doesn't involve SOME type of accommodation on your part.
I am not sure where to look for what character set I am using, but I don't use any of the extended ASCII characters, only the first 128 characters. So UTF-8 should be identical to ASCII code order. It appears to me that Access has created its own non-ASCII character set encoding so that all punctuation/special characters precede all alphanumeric characters in their encoding.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:06
Joined
Feb 28, 2001
Messages
22,832
Where do I find the Option for Compare Binary so that I can switch between these options to see how they work. I couldn't find it in Options

I'm answering this separately because I'm not sure I can answer that, either. But on my Ac2010, File>>Options>>General includes a "New Database Sort Order" list-box in the middle of that box.
 

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
Since I don't know your field settings and character-set choices, I can't tell you why anything sorts in any particular way. All I can tell you is that like it or not, SOMETHING is leading to that sort order. Let me ask one more question. Since it appears that you are using a proportional font, is it possible that a leading space exists (to the immediate left of a "{" character)? Because of kerning, a leading space before a special character would not be very big because kerning DOES depend on the next character. If you have a leading space there to the left of the special characters, then they would appear in the presented order. WHY you would have a space there, I cannot know. But it would go a long way towards explaining the presented order.
Thank you. All promising things to consider. There are no leading spaces however. I did not create this DB, I simply took it over. So I will need to figure out how to look inside at the properties for the field itself to see if there is something there that might impact on the encoding and the order.
 

iJimJ

New member
Local time
Today, 11:06
Joined
Jun 8, 2022
Messages
8
I'm answering this separately because I'm not sure I can answer that, either. But on my Ac2010, File>>Options>>General includes a "New Database Sort Order" list-box in the middle of that box.
Thank you-- I found it. The only non-language options that I tried were "General" and "General - Legacy" but I saw no differences in outcome. But then again, this is not a "New Database" as noted in the label for that option.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:06
Joined
Feb 19, 2002
Messages
36,365
You learn something new every day.

Option Compare Statement - Visual Basic | Microsoft Docs

I'm going to guess that changing to binary, if you succeed will result in other issues that will cause more pain.

I would go with a separate field to force the errors to the bottom if that is what you want. You don't need to actually add a new column to the table, you can come up with it on the fly.

Select ...
From ...
Order By IIf(Left(Yourfield,1) = "{", 9, 0), YourField);

So if you enclose the names in {} when they are bad, the Left() assigns 9, otherwise 0 and that is the primary sort field. Then the second sort field is the data field. That will push the {} names to the bottom of the list.
 

Isaac

Lifelong Learner
Local time
Today, 09:06
Joined
Mar 14, 2017
Messages
6,558
That does not appear to be the case. ASCII codes for {, }, | and ~ are higher than all alphanumeric and other special characters, therefore strings that begin with those characters should be at the end of an ascending sort. However, all strings that begin with any special character rise to the top of an ascending order sort (including these 4 characters).
Why not just return the ASCI codes and then sort by it? @Gasman on a replicated DB on my end, your method (which I also recommend to op) works fine.
 

Users who are viewing this thread

Top Bottom