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

• Microsoft Access 97

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 

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 

Do Until MyVar1 = Val1 And MyVar2 = Val2
   ' Perform some action.
Loop 

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.



2. Open the Products table in Datasheet view, and then check to make sure you have a record with ProductName = Longlife Tofu and ProductID = 74.



3. Add a new record with the following values:




   ProductID = 78
   ProductName = Longlife Tofu 

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

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


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


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


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

Additional query words: prb opposite reversed

Keywords : kbdta PgmOthr
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb