ACC97: Select Statements in Queries Do Not Work

• Microsoft Access 97

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 Software Library.

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

Q172733 ACC97: Updated Version of Microsoft Jet 3.5 Available on MSL

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          : kbdta 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: May 4, 1999