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

ID: Q208579

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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 later, which use 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 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 follows.

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

MORE INFORMATION

Steps to Reproduce Behavior

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

Open the Products form in Design view and set the OnCurrent property of the form 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

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.

Start Microsoft Access 2000, and repeat steps 1 through 3. In step 2, change the first line of code that reads


Sub Form_Current()


Private Sub Form_Current()

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, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Boolean data type" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: prb

Keywords : kbdta CnvOthr PgmOthr
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb