Microsoft KB Archive/236370

= ACC97: Do...Loop That Uses Negative Logic Reverses Behavior of AND and OR Operators =

Article ID: 236370

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q236370



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



SYMPTOMS
When you use a Do...Loop that contains negative logic in the criteria of either the loop or in any logical test within the loop, the logical operators AND and OR reverse their behavior. For example, if you were to use either of the following code structures Do While MyVar1 <> Val1 And MyVar2 <> Val2 ' Perform some action. Loop

-or-

Do Until rs.EOF If MyVal1 <> Val1 And MyVal2 <> Val2 Then ' Perform some action. End If rs.MoveNext Loop the loop would end when either condition is true, rather than when both are true. If you were to use the OR operator instead of AND in the preceding code structures, the loop would not end until both conditions were true.



RESOLUTION
Use positive logic in the criteria line. For example, instead of using logic such as Do While MyVar1 <> Val1 And MyVar2 <> Val2 ' Perform some action. Loop use logic such as: Do Until MyVar1 = Val1 And MyVar2 = Val2 ' Perform some action. Loop



Steps to Reproduce Behavior
 Open the sample database Northwind.mdb. Open the Products table in Datasheet view, and then check to make sure you have a record with ProductName = Longlife Tofu and ProductID = 74.  Add a new record with the following values:   ProductID = 78 ProductName = Longlife Tofu   Insert a new module and type the following code: Option Compare Database Option Explicit

Public Sub DoWhileIf

Dim db As DAO.Database Dim rs As DAO.Recordset

Set db = CurrentDb Set rs = db.OpenRecordset("Select * from Products")

With rs     Do Until .EOF If !ProductName <> "Longlife Tofu" And !ProductID <> 78 Then .MoveNext Else Debug.Print "Found it! " & !ProductName & " " & !ProductID Exit Sub End If     Loop End With

rs.Close Set rs = Nothing Set db = Nothing

End Sub

Public Sub DoWhileAnd

Dim db As DAO.Database Dim rs As DAO.Recordset

Set db = CurrentDb Set rs = db.OpenRecordset("Select * from Products")

With rs     Do While !ProductName <> "Longlife Tofu" And !ProductID <> 78 .MoveNext Loop Debug.Print "Found it! " & !ProductName & " " & !ProductID End With

rs.Close Set rs = Nothing Set db = Nothing

End Sub

Public Sub DoWhileOr

Dim db As DAO.Database Dim rs As DAO.Recordset

Set db = CurrentDb Set rs = db.OpenRecordset("Select * from Products")

With rs     Do While !ProductName <> "Longlife Tofu" Or !ProductID <> 78 .MoveNext Loop Debug.Print "Found it! " & !ProductName & " " & !ProductID End With

rs.Close Set rs = Nothing Set db = Nothing

End Sub

Public Sub DoWhilePos

Dim db As DAO.Database Dim rs As DAO.Recordset

Set db = CurrentDb Set rs = db.OpenRecordset("Select * from Products")

With rs     Do Until !ProductName = "Longlife Tofu" And !ProductID = 78 .MoveNext Loop Debug.Print "Found it! " & !ProductName & " " & !ProductID End With

rs.Close Set rs = Nothing Set db = Nothing

End Sub   In the Debug window, type the following line, and then press ENTER: DoWhileIf Note that the code unexpectedly exits the loop on the record where ProductID = 74.

</li>  In the Debug window, type the following line, and then press ENTER: DoWhileAnd Note that the code unexpectedly exits the loop on the record where ProductID = 74.

</li>  In the Debug window, type the following line, and then press ENTER: DoWhileOr Note that the code unexpectedly exits the loop on the record where ProductID = 78.

</li>  In the Debug window, type the following line, and then press ENTER: DoWhilePos Note that the code exits the loop, as expected, on the record where ProductID = 78. </li></ol>

Additional query words: prb opposite reversed

Keywords: kbprb KB236370

-

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

© Microsoft Corporation. All rights reserved.