IIf question

[Victoria]

I recently read that there is a danger in using IIf in VBA because both
outcomes are evaluated whether this is necessary or not. For example, the
following code crashes, even though division by 0 isn't an expected outcome.

dblValue = IIf(intP = 0, 0, intQ/intP)

This concerns me because I have lots of IIf statements in my SQL WHERE
clauses, though I haven't noticed a problem. Does SQL have the same
difficulty with IIf that VBA does?

Vicky

 

[Douglas J. Steele]

The IIf statement behaves differently in SQL statements than in VBA.

 

[Jack Leach]

Is there some specific reason VBA Iif acts this way? I can't seem to think
of anything...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Douglas J. Steele]

I have no idea why.

 

[Douglas J. Steele]

Since it's only an issue in VBA, why not just use

If intP = 0 Then
dblValue = 0
Else
dblValue = intQ/intP
End If

?

 

[Dirk Goldgar]

Is there some specific reason VBA Iif acts this way? I can't seem to
think
of anything...

In order to evaluate a function In VBA, the function's arguments must first
be evaluated. Only then can the results of those evaluations be passed to
the function. In this, the VBA IIf() function is like any other function.
All three arguments are evaluated, and then the function just has to check
the truth value of the first argument value and decide which of the other
two argument values should be returned.

Jet/ACE SQL has an entirely different, native implementation of IIf(), which
does not involve evaluating the arguments first. I don't know the inner
details of how this is done, but it's my understanding that a SQL statement
is always parsed into an execution plan, which is a series of steps that
will be executed to get the result set. Thus, it seems reasonable (to me)
that a SQL expression such as,

IIf(A= 0, 0, B/A)

.... would be translated into something similar to an If/Then/Else block like
this:

If A = 0 Then
Return 0
Else
Return (B / A)
End If

If interpreted like that, then only the "true" result would ever be
evaluated.

 

[Jack Leach]

Didn't know that. Thanks Dirk!

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Victoria]

Thanks to all for great help - Vicky

In order to evaluate a function In VBA, the function's arguments must first
be evaluated. Only then can the results of those evaluations be passed to
the function. In this, the VBA IIf() function is like any other function.
All three arguments are evaluated, and then the function just has to check
the truth value of the first argument value and decide which of the other
two argument values should be returned.

Jet/ACE SQL has an entirely different, native implementation of IIf(), which
does not involve evaluating the arguments first. I don't know the inner
details of how this is done, but it's my understanding that a SQL statement
is always parsed into an execution plan, which is a series of steps that
will be executed to get the result set. Thus, it seems reasonable (to me)
that a SQL expression such as,

IIf(A= 0, 0, B/A)

... would be translated into something similar to an If/Then/Else block like
this:

If A = 0 Then
Return 0
Else
Return (B / A)
End If

If interpreted like that, then only the "true" result would ever be
evaluated.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)