Microsoft KB Archive/230559

= 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.



Steps to Reproduce Problem
 Start Microsoft Access 97. Create a new database.  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  Press CTRL+G to display the Debug window.</li> 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.</li></ol>

Additional query words: pra

Keywords: kbbug kbfix KB230559

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.