Microsoft KB Archive/230559

Knowledge Base

ACC97: Select Statements in Queries Do Not Work

Article ID: 230559

Article Last Modified on 1/24/2007

---

APPLIES TO

• Microsoft Access 97 Standard Edition

---

This article was previously published under Q230559

Advanced: Requires expert coding, interoperability, and multiuser skills.

SYMPTOMS

Two logically identical queries provide different result sets under certain circumstances.

CAUSE

A SQL statement with multiple field criteria that are based on highly non-selective indexes will not return any records if all of the following conditions are true:

• Criteria are not translated into expressions during the compile stage of the query optimizer.
• Criteria contain a combination of AND and OR.
• Criteria contain at least 1 nonindexed field.
• Number of records in table > ~200.

RESOLUTION

Obtain Microsoft Jet Database Engine 3.51 Service Pack 2, which is available from the Microsoft Download Center.

For information on how to obtain Microsoft Jet Database 3.51 Service Pack 2, please see the following article in the Microsoft Knowledge Base:

172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Jet Database Engine 3.51 Service Pack 2.

MORE INFORMATION

Steps to Reproduce Problem

1. Start Microsoft Access 97.
2. Create a new database.

3. Paste the following code into a module in the new database:

   Sub MakeTestTable(NumRecs As Long)
   Dim n As Integer
   Dim rs As Recordset
     On Error Resume Next
       CurrentDb.Execute "Drop Table Test"
     On Error GoTo 0
     CurrentDb.Execute "Create Table Test(F1 Long, F2 long, F3 Long, F4 Long)"
     CurrentDb.Execute "Create Index F1 ON Test(F1)"
     ' CurrentDb.Execute "Create Index F2 ON Test(F2)"
     CurrentDb.Execute "Create Index F3 ON Test(F3)"
     CurrentDb.Execute "Create Index F4 ON Test(F4)"
     Set rs = CurrentDb.OpenRecordset("Test")
     For n = 1 To NumRecs
       With rs
         .AddNew
           !F1 = 1
           !F2 = 1
           !F3 = 1
           !F4 = 1
         .Update
         .AddNew
           !F1 = 0
           !F2 = 0
           !F3 = 0
           !F4 = 0
         .Update
       End With
     Next n
   End Sub
   
   Sub ShowProblem()
   Dim rsFail As Recordset
   Dim rsPass As Recordset
   ' The only difference between the two sql statements are the brackets
   ' around the last 2 criteria
   Set rsFail = CurrentDb.OpenRecordset("SELECT count(*) as out FROM test" & _
       " WHERE(f1=1 AND (f2=0 OR f3=0 OR f4=1));")
   Set rsPass = CurrentDb.OpenRecordset("SELECT count(*) as out FROM test" & _
       " WHERE(f1=1 AND (f2=0 OR (f3=0 OR f4=1) ));")
   MsgBox "rsFail   record count = " & rsFail!Out & vbCrLf & _
     "rsPass record count = " & rsPass!Out
   End Sub

4. Press CTRL+G to display the Debug window.
5. In the Debug window, run the following commands:

   MakeTestTable 100
   ShowProblem

   Note that the message box displayed by the Showproblem routine will show 0 records for the first query and 100 for the second query.

Additional query words: pra

Keywords: kbbug kbfix KB230559

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.