A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IIf function Problem



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2010, 05:51 PM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default 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  
Old February 11th, 2010, 05:53 PM posted to microsoft.public.access.forms
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old February 11th, 2010, 06:01 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old February 11th, 2010, 06:31 PM posted to microsoft.public.access.forms
Pamela
external usenet poster
 
Posts: 193
Default 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  
Old February 11th, 2010, 06:46 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.