Help using AND OR in IIF statement

[oliviar]

Hi Guys,
I want to do this:
iif([POP] = 1 OR 2 OR 3,[POP]+1,[POP])

When I wrote it like that, it just added 1 to everything - even if the POP wasn't 1,2,or 3.

What am I doing wrong?

 

[oliviar]

Okay,
I ended up using a MASSIVE nested IIF statement.

But is there a BETTER way?

 

[xenou]

Maybe:
IIf( ([POP] = 1) OR ([POP] = 2) OR ([POP] = 3), [POP]+1, [POP])

 

[revans]

Hi Guys,
I want to do this:
iif([POP] = 1 OR 2 OR 3,[POP]+1,[POP])

When I wrote it like that, it just added 1 to everything - even if the POP wasn't 1,2,or 3.

What am I doing wrong?

The IIf statement's syntax is IIf(expression, Truepart, Falsepart). It evaluates the expression and if it evaluates to True, it returns the Truepart, otherwise the Falsepart. When you wrote "[POP] = 1 OR 2 OR 3" for the expression you really wrote "([POP] = 1) OR (2) OR (3)". The (2) and (3) will always evaluate to True since the expression 2 is the same as (2=2). This is the same for any number left alone like that. Since you're using OR any part of that expression that evaluates to True will cause the IIf to return the Truepart, thus it will always add 1 to everything as you found.

Xenou's method will get around this.

hth,

Rich

 

[oliviar]

I'm sorry, I still don't understand why the language would do POP+1 if POP=5 in my original version.


I want to get inside its head and understand how it works....
?

 

[xenou]

everything in the OR clause must evaluate to TRUE or FALSE.

If [POP] is 5 then all of these are FALSE:

[POP] = 1
[POP] = 2
[POP] = 3

But if you write this:

(1 OR 2 OR 3)

The answer is always TRUE. 1 is TRUE. 2 is TRUE. 3 is TRUE. Anything but 0 is always TRUE.

 

[oliviar]

hmmm. I still don't understand how ([5]=1 or 2 or 3) is true, but ([5]=0) is false, but I am probably annoying you know and will just accept it as a quirk of VB.

 

[boblarson]

The situation is that you need to provide the ENTIRE comparison for each value and not just, as you have found out, the first one. The other option depending on what you have for other options is to use this to shorten it:

IIf([POP]>=1 OR [POP]<=3, [POP] + 1, [POP])

 

[oliviar]

I see. It really is another language isn't it?!

 

[xenou]

Another way of writing your incorrect statement is:

x = 5
If Not x = 1 Then
    If 2 Then
        If 3 Then
            x = 5 + 1
        Else
            x
        End If
    End If
End If

Another way of writing the correct statement is:

x = 5
If Not x = 1 Then
    If Not x = 2 Then
        If Not x = 3 Then
            x = 5 + 1
        Else
            x
        End If
    End If
End If