How to Resolve Type Mismatch Runtime "error code 13" MS Access

Question

I have created two simple VBA code modules in MS Access.

1) This one works great -

Private Sub IFLType_BeforeUpdate(Cancel As Integer)
If [ProductType] <> "IFL" Then
  If [IFLType] <> IsNotNull Then
    MsgBox ("IFLType only valid for ProductType = IFL")
  End If
End If
End Sub

2) This one produces Type Mismatch Runtime error #13 and the debugger highlights the line with the "Or" logic -

Private Sub ProductDue_BeforeUpdate(Cancel As Integer)
If [ProductType] <> "IFL" Or "3-TIER IPRN" Or "CD IPRN" Then
  If [ProductDue] <> IsNotNull Then
    MsgBox ("ProductDue only valid for ProductType = IFL, 3-TIER IPRN, and CD IPRN")
  End If
End If
End Sub

The only significant difference between these is the "Or" logic. Any ideas for how to write the "Or" logic and make this work?

Answer 1

You need to write the complete or statement, like this:

If [ProductType] <> "IFL" Or [ProductType] <> "3-TIER IPRN" Or [ProductType] <>  "CD IPRN" Then ...

Answer 2

Due to operator precedence in VBA, comparison operators (such as <>) will be evaluated before logical operators (such as Or); as such, you will need to include a comparison operator between each of your logical operators, i.e.:

Private Sub ProductDue_BeforeUpdate(Cancel As Integer)
    If [ProductType] <> "IFL" Or [ProductType] <> "3-TIER IPRN" Or [ProductType] <> "CD IPRN" Then
        If [ProductDue] <> IsNotNull Then
            MsgBox ("ProductDue only valid for ProductType = IFL, 3-TIER IPRN, and CD IPRN")
        End If
    End If
End Sub

However, based on the message you are reporting to the user, I believe you will actually require And operators in place of Or operators, since, if [ProductType] is equal to "3-TIER IPRN" then the Or statement will return True for [ProductType] <> "IFL" and the test expression for the If statement will be validated.

Therefore, I believe your test should be:

Private Sub ProductDue_BeforeUpdate(Cancel As Integer)
    If [ProductType] <> "IFL" And [ProductType] <> "3-TIER IPRN" And [ProductType] <> "CD IPRN" Then
        If [ProductDue] <> IsNotNull Then
            MsgBox ("ProductDue only valid for ProductType = IFL, 3-TIER IPRN, and CD IPRN")
        End If
    End If
End Sub

Or alternatively (and maybe more readable):

Private Sub ProductDue_BeforeUpdate(Cancel As Integer)
    If Not ([ProductType] = "IFL" Or [ProductType] = "3-TIER IPRN" Or [ProductType] = "CD IPRN") Then
        If [ProductDue] <> IsNotNull Then
            MsgBox ("ProductDue only valid for ProductType = IFL, 3-TIER IPRN, and CD IPRN")
        End If
    End If
End Sub

You could also combine your second test expression with the first to avoid the nested If statement, i.e.:

Private Sub ProductDue_BeforeUpdate(Cancel As Integer)
    If [ProductDue] <> IsNotNull And (Not ([ProductType] = "IFL" Or [ProductType] = "3-TIER IPRN" Or [ProductType] = "CD IPRN")) Then
        MsgBox ("ProductDue only valid for ProductType = IFL, 3-TIER IPRN, and CD IPRN")
    End If
End Sub