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


The information in this article applies to:

  • Microsoft Access 97
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

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.
Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kbprb KB236370
©2003 Microsoft Corporation. All rights reserved.