PRB: Sort Order Has Changed with Microsoft Jet version 4.0 (236952)



The information in this article applies to:

  • Microsoft Data Access Components 2.5
  • Microsoft ODBC Driver for Access 4.0
  • Microsoft OLE DB Provider for Jet 4.0
  • Microsoft Access 2000

This article was previously published under Q236952

SYMPTOMS

Using Microsoft Jet version 4.0, sorting is different than in previous versions. For example, assume you have the following records in an Access table:

FIELD1
W
W-
WB
W-B
In previous versions of the Jet database engine, issuing "SELECT * FROM Table1 ORDER BY field1" gives the following results:
FIELD1
W
W-
W-B
WB
With Jet version 4.0, you now get the following results:
FIELD1
W
W-
WB
W-B

CAUSE

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.

RESOLUTION

This behavior is by design. You cannot change any settings to affect this behavior.

MORE INFORMATION

SQL Server full-text queries and Index server have similar behavior when searching.

For additional information, please see the following article in the Microsoft Knowledge Base:

200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS Queries


Modification Type:MajorLast Reviewed:9/24/2003
Keywords:kbDatabase kbDTL kbJET kbprb KB236952 kbAudDeveloper