ACC2000: Query Returns no Records with an Indexed Field That Contains Dashes (271661)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q271661
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you run a query against data that contains dashes, and you are using the LIKE operator as criteria in the query, no rows are returned.

CAUSE

The field that contains the dashes is indexed. The LIKE operator does not work for this field in this case.

RESOLUTION

The following updates are required:
  1. You must obtain the latest Microsoft Jet 4.0 service pack that contains an updated version of the Microsoft Jet 4.0 database engine. For additional information about how to obtain Microsoft Jet 4.0, click the following article number to view the article in the Microsoft Knowledge Base:

    239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

  2. You must also install one of the following to obtain the updated mswdat10.dll and mswstr10.dll files that are version 4.0.3829.2 or later:
    • Install Microsoft Data Access Components MDAC 2.5 SP1 (GA). You can download MDAC 2.5 SP1 (GA) from the following Microsoft Web site: http://msdn.microsoft.com/dataaccess
    • Obtain Microsoft Windows 2000 Service Pack 1 (Win 2000 SP1). For information about downloading SP1 or obtaining the Windows 2000 SP1 CD, please visit the following Microsoft Web site: http://www.microsoft.com/windows2000/
    • Install Microsoft SQL Server 2000. For information about SQL Server 2000, please visit the following Microsoft Web site: http://www.microsoft.com/sql/

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new Access 2000 database.
  2. Create a table named Table1 that has a single Text field named Field1.
  3. Add an index to the Field1 field.
  4. Switch the Table1 table to Datasheet view.
  5. Add the following data to the Field1 field:
       g2-1-40
   g2-1-41
   g2-1-42
   g2-1-43
   g2-1-44
  6. Close and save the table.
  7. On the Insert menu, click Query. Click OK in the New Query dialog box to open the Query Designer. Close the Show Tables dialog box without adding any tables.
  8. In the Query Designer, click SQL View on the View menu.
  9. Type the following into the SQL view:
       SELECT Table1.field1
   FROM Table1
   WHERE (((Table1.field1) Like "g2-1*"));
  10. Run the query. Note that no rows are returned.
  11. Close and save the query as Query1.
  12. Open the Table1 table in Design view, and then remove the index from the Field1 field.
  13. Close and save the table.
  14. Run the Query1 query again. Note that now the correct rows are returned.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbbug kbpending KB271661 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.