IIF surprise 1

[MasterRacker]

I was using a IIF to avoid a divide by zero problem. The IIF did not work, expanding to a full If-Then-Else structure did work correctly.

Is there some subtlety I'm missing or is this a compiler quirk to beware of?

Does NOT work:
            avgPrice = IIf((onHand![onHand] < 1), 0, runningTotal / onHand![onHand])

Works:
            If (onHand![onHand] < 1) Then
                avgPrice = 0
            Else
                avgPrice = runningTotal / onHand![onHand]
            End If

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[SkipVought]

There is no IIF in VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MasterRacker]

I respectfully beg to differ. Try this:

Sub test()
    Dim str As String
    Dim num As Integer
    
    str = "b"
    num = IIf(str = "a", 10, 12)
    
    Debug.Print num
End Sub

Works perfectly. Intellisense brings up options as well.

(Using Access 2007 on a 2003 compatible DB.)


Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

I believe there is a difference between how IIf() is implemented in queries and in code. Apparently both the True and False expressions are evaluated in code but I think not in queries.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Well how 'bout that. Always thot it was a Jet query thang.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Hmmmmm.

Works for me.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Duane,
That is 100% correct. According to the helpfile

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True

But it will short circuit in a query.

 

[MajP]

What is even stranger it will short circuit in a calculated control on a form or report along with queries.

 

[MasterRacker]

MajP - That's exactly it. I was still bombing on the divide by zero condition although it didn't appear that I should be. Now we all know.


Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]