ACC: Yes/No Field Not Evaluating "-1" or "0" in Comparisons

Last reviewed: May 30, 1997
Article ID: Q142229
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Code written in Access Basic that uses "-1" or "0" (with the quotation marks) to compare a Yes/No field in a table or recordset does not work properly when you convert the Access Basic code to Visual Basic for Applications code. In Microsoft Access version 2.0, which uses Access Basic, the numeric comparisons are evaluated as strings. In Microsoft Access 7.0 and 97, which uses Visual Basic for Applications, the numeric comparisons are evaluated as Boolean values.

CAUSE

In Access Basic the condition

   If rs![yesnofield] = "-1" Then

is evaluated as True, and the condition:

   If rs![yesnofield] = "0" Then

is evaluated as False.

In Visual Basic for Applications, these conditions are no longer evaluated in this manner.

RESOLUTION

In an open Access Basic or Visual Basic for Application Module window, use the Find command on the Edit menu (or press CTRL+F) to check for instances of the strings "-1" or "0" (with the quotation marks).

Change all instances in Visual Basic for Applications where "-1" or "0" is used to compare a Yes/No field in a table or recordset as described below.

If you are using the following expression in Access Basic

   IF rs![yesnofield] = "-1" Then

in Visual Basic for Applications change the code so that there are no quotation marks around the number "-1" or use the word "True" as in the following example:

   IF rs![yesnofield] = -1 Then

   -or-

   IF rs![yesnofield] = True Then

If you are using the following expression in Access Basic

   IF rs![yesnofield] = "0" Then

in Visual Basic for Applications, change the code so that there are no quotation marks around the number "0" or use the word "False" as in the following example:

   IF rs![yesnofield] = 0 Then

   -or-

   IF rs![yesnofield] = False Then

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access 2.0 and open the sample database NWIND.MDB.

  2. Open the Products form in Design view and set the form's OnCurrent property to the following event procedure:

          Sub Form_Current()
          Dim rs As Recordset
             Set rs = Me.RecordsetClone
             rs.Bookmark = Me.Bookmark
             If rs![Discontinued] = "-1" Then
                MsgBox "This product is discontinued."
             End If
             Set rs = Nothing
          End Sub
    
    

  3. Open the Products form in Form view and, using the record selectors, move through the records. Note that a message box appears for all discontinued products.

  4. Start Microsoft Access 7.0 or 97, and repeat steps 1-3. In step 2, change the first line of code that reads

          Sub Form_Current()
    
       to read as follows:
    
          Private Sub Form_Current()
    
    

  5. Open the Products form in Form view and, using the record selectors, move through the records. Note that the message box for the discontinued products does not appear.

REFERENCES

For more information about the Boolean data type, search the Help Index for "Boolean data type," or ask the Microsoft Access 97 Office Assistant


Keywords : CnvGnrl kbusage
Version : 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.