PRB: Sort Order Has Changed with Microsoft Jet version 4.0

• Microsoft ODBC Driver for Access, version 4.0
• Microsoft OLE DB Provider for Jet, version 4.0
• Microsoft Access 2000

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:

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

Additional query words: DAO 3.6 ODBC OLE DB dash mdac 2.1

Keywords : kbDAO kbDatabase kbDTL kbJET kbMFC kbODBC kbOLEDB kbVBp600 kbVC kbVJ kbDSupport kbDAO360
Version : WINDOWS:2000,4.0
Platform : WINDOWS
Issue type : kbprb