IIf function Problem

  • Thread starter Thread starter Pamela
  • Start date Start date
P

Pamela

I am getting an error message that indicates that Access is still trying to
perform the DLookup or "False" when the result should be "True".
Here is my code:
IIf(IsNull(Me.Days), "No repair time should be allotted.", _
DLookup("Return", "ltblDays", "Lookup = " & Me.Days))
So when Me.Days is tabbed into and out of w/o entry, or an entry is made and
then deleted, the DLookup attempts to run but is null and so cannot complete
it's Dlookup. Any ideas why the code isn't recognizing a True statement and
stopping before running the False??
Thanks so much!
Pamela
 
In VBA, Access always attempts to run both parts of the IIf statement. The
behaviour's different when using IIf statements in queries.
 
Pamela said:
I am getting an error message that indicates that Access is still trying to
perform the DLookup or "False" when the result should be "True".
Here is my code:
IIf(IsNull(Me.Days), "No repair time should be allotted.", _
DLookup("Return", "ltblDays", "Lookup = " & Me.Days))
So when Me.Days is tabbed into and out of w/o entry, or an entry is made
and
then deleted, the DLookup attempts to run but is null and so cannot
complete
it's Dlookup. Any ideas why the code isn't recognizing a True statement
and
stopping before running the False??
Click to expand...


There are two different IIf functions: one that is part of VBA (and so
operates in code procedures), and one that is part of the Jet database
engine (and so operates in queries and in controlsource expressions). In
VBA version, all arguments are first evaluated, and then passed to the
function, which returns one or the other of the "result" arguments depending
on the truth value of the condition argument. In the Jet version, only
argument that is actually going to be returned is evaluated.

You can solve your problem either by rewriting the code to use an
If/Then/Else construction, like this:

If IsNull(Me.Days) Then
<something> = "No repair time should be allotted."
Else
<something> = DLookup("Return", "ltblDays", "Lookup = " & Me.Days)
End If

.... or else by framing the DLookup so that it won't raise an error if
evaluated when Me.Days is Null:

<something> = IIf(IsNull(Me.Days), _
"No repair time should be allotted.",
DLookup("Return", "ltblDays", "Lookup = " & Nz(Me.Days, 0))

The former is more efficient, since it avoids an unnecessary call to
DLookup, and so should generally be preferred.
 
Hi Dirk,

Thanks so much for that response. I had to add a 3rd closed parenthasis )))
to the formula, but then it worked perfectly.

Thanks so much!!!
 
Pamela said:
Thanks so much for that response. I had to add a 3rd closed
parenthasis )))
to the formula, but then it worked perfectly.
Click to expand...


Sorry about the error, though I really think you ought to use the
If/Then/Else block instead of the IIf.
 
Back
Top