IF value OR value Then [closed]

Question

Using Access VBA we need to do an IF-Than statement but there are 16 values. So, I have tried including "OR" but this code returns error "Can't find the field 'I' referred to in the expression. Here is what we have: Note: the fields are on a subform f_SubReviewer:: If (Me![f_SubReviewer]![ReviewerComments] <> "" Or [Adj1] <> "") Than...

Answer 1

This sounds as if an if with 16 ors is the wrong way. On the other hand, the case statement seems perfect for this.

A quick search show that MS Access has a case statement, with the following syntax:

Select Case test_expression
   Case condition_1
     result_1
   Case condition_2
     result_2
   ...
   Case condition_n
     result_n

  Case Else
     result_else

End Select

That would result in more readable code, which might also be faster.

---

An example of using switch and case vs if (using C like syntax since I know that one)

If( (A=1) | (A=2) | (A=3) )
{
  Do something
}
Else
{
  Do something else
}

switch( A )
{
   case 1:  {Do something} ; break
   case 2:  
   case 3: {Do something} (A might be 2 or 3 since there was no break behind 2); break.
   Default: {Do something else}
}

Answer 2

It looks like you're trying to check several fields/variables to make sure none is empty or null. In this case, you could do a whole bunch of nested if / elseif statements, but that can get ugly fast. I would suggest creating a new boolean variable like blnNoBlanks, set it to true and then run individual if statements on each field, and set the variable to false if any of the conditions fails. Like this:

Dim blnNoBlanks as boolean
blnNoBlanks = True
If (Me![f_SubReviewer]![ReviewerComments] = "" Then 
     blnNoBlanks = false
End If 
If [Adj1] = "" Then
     blnNoBlanks = false
End If
'... the rest of the IF statements
If blnNoBlanks Then
     'Enter your new code here
End If

If the blnNoBlanks variable makes it through the gauntlet of IF then you are good to go. If not, the variable will be false and you can act accordingly. Even better than the above code would be to put all the checks into a function and just call it to assign your boolean variable. It would keep the code easier to read and would allow you to call that function again from other locations in your code.