Access™ 2007 VBA Programmer's Reference

8.3. Evaluating Expressions in VBA

Expressions are one of the basic building blocks of any programming language. There are several ways to evaluate expressions in VBA so that you can control the flow of your procedural logic.

8.3.1. If .. Then

Nearly, every programming language has some way of asking If, and VBA is no exception. The If..Then structure is one of the most commonly used in VBA. Its usage is straightforward, but there are a couple of issues that warrant extra attention. First, the expression you are using needs to be formed correctly and completely. One common mistake is to use an expression like this:

If intOrderStatus = 1 Or 2 Then
  'some interesting code here
End If

The problem here is that a complete Boolean (true or false) expression needs to be on both sides of the Or. The literal way to interpret this expression is "if intOrderStatus = 1 or if 2 is True, then," which, of course, makes no sense. The value 2 is not true. In fact, in Access VBA any value other than −1 is false, so the value 2 is always false. This If statement has a big problem—the interesting code will run if the order status is 1, but it will never run if it is 2.

The correct way to write this line of code is as follows:

If intOrderStatus = 1 Or intOrderStatus = 2 Then
  'some interesting code here
End If

It's repetitive, but you have to tell VBA exactly what you want to do.

Instead of using multiple Or operators in SQL statements, you can use a much easier syntax: the In operator. In SQL, the ...