Sort Question

[Guest]

I have a field that when I sort ignores the dashes in the data.

ie
A-P
ACS

shows up as:
ACS
A-P

is this normal? This does not seem to be the same on the Mac when I sort.

Thanks,

Ken

 

[Steve Schapel]

Ken,

Yes, this is the way Access interprets the sort order of the data. It
is unfortunate that this sometimes produces confusing or unexpected
results, especially since it actually varies between different versions
of Access. As far as I know, you just have to learn to live with it.

 

[Jeff Boyce]

Ken

I believe this is related to the attempt to make "O'Brien" sort before
"Occidental" (note placement of symbol).

In your situation, "AC" comes before "AP".

Like Steve, I haven't found a way around it...

Good luck

Jeff Boyce
<Access MVP>

 

[Dodo]

I have a field that when I sort ignores the dashes in the data.

ie
A-P
ACS

shows up as:
ACS
A-P

is this normal? This does not seem to be the same on the Mac when I
sort.

Thanks,

Ken

Do you want to have the data sorted in a form or report?
Have you based the form/report on a query?

If so, add an expression to the query that takes the unwanted characters
out of the field concerned (use replace) and sort the data in the query by
that expression!
The form/report will then follow the "human" alphabetical order.

 

[Jeff Boyce]

If you take the "-" out of the character string, won't that still sort as

ACS
AP

?Perhaps if you added a "-" to every string (that doesn't already have one)?

Jeff Boyce
<Access MVP>

 

[Dodo]

If you take the "-" out of the character string, won't that still sort
as

ACS
AP

I apparently did not catch the original poster's problem.

Why would one like to have A-P before ACS?

That is a cybersort!

 

[Guest]

Yes I want the A-P to come before the ACS. This is in a field on a form
(actually a sort field) so I can not really use a query.

Thanks for the help,

Ken

 

[Ronald Roberts]

Look at any Ascii or EBCDIC or BCD character set table.
This is the collating sequence for the characters.
Special characters, numbers, Cap letters, Small letters.
The computer requires this. All programming languages,
not just Access, will see the same sort order.
What is confusing is why characters from 58 to 64 and
123 to 127 decimal are not with the special characters before
the zero decimal 48 when the code was created.
New character were added when the code was expanded from 127 to 255.

These results are not unexpected. Also I have never seen different
results in different version of Access. The computer world could not
operate if the sort order varied between different OS's or versions
of software and surely no hardware would work correctly, especially
printers.

Ron

 

[Ronald Roberts]

I stand corrected About Access, but the problem is really a Jet problem.

PRB: Sort Order Has Changed with Microsoft Jet version 4.0
View products that this article applies to.
Article ID : 236952
Last Review : September 24, 2003
Revision : 2.0

This article was previously published under Q236952
Microsoft Jet version 4.0 uses a new sorting order that treats hyphens
(-) and single quotation marks (') as special characters. For example,
Co-op and Coop are now considered equal when sorting. Also O'Connor is
treated the same as OConnor.

This makes no sense to me why Microsoft would do this.
These are the results I got.

Access97 A, A-C, ACP
Access2003 A, A-C, ACP
Access2000 A, ACP, A-P
Access2002 A, ACP, A-P