If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIf function Problem
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 |
#2
|
|||
|
|||
IIf function Problem
In VBA, Access always attempts to run both parts of the IIf statement. The
behaviour's different when using IIf statements in queries. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Pamela" wrote in message ... 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 |
#3
|
|||
|
|||
IIf function Problem
"Pamela" wrote in message
... 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?? 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. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#4
|
|||
|
|||
IIf function Problem
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!!! "Dirk Goldgar" wrote: "Pamela" wrote in message ... 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?? 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. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#5
|
|||
|
|||
IIf function Problem
"Pamela" wrote in message
... Thanks so much for that response. I had to add a 3rd closed parenthasis ))) to the formula, but then it worked perfectly. Sorry about the error, though I really think you ought to use the If/Then/Else block instead of the IIf. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|