Microsoft KB Archive/142229

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

Article ID: 142229

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q142229



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



SYMPTOMS
Code written in Access Basic that uses -1 or 0 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 Access version 2.0, which uses Access Basic, the numeric comparisons are evaluated as strings. In 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.

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



STATUS
This behavior is by design.



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 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 </li> 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.</li>  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 </li> 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.</li></ol>

<div class="references_section">