Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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...

share|improve this question

closed as not a real question by Oliver Salzburg Sep 11 '12 at 23:10

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
This should really be on StackOverflow since it is a programming question. –  techturtle Sep 11 '12 at 22:05

2 Answers 2

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}
}
share|improve this answer

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.