How to code IIf test

[John W. Vinson]

On Wed, 10 Jun 2009 16:02:04 -0700, Scientific


IIF(<some logical expression>, <value if true>, <value if false>)

For a more detailed answer please post a more detailed question.

 

[Scientific]

Douglas, John,

Sorry, I hit an errant key and posted prematurely.

I've been trying new ways to return data in a form control using a query and
DLookUp. However, I noticed if you use DLookUp to search a field that's null
you get an error that looks like "#Error". As an example, if I was using the
code below in a control how could I use IIf to tell DLookUp not to search on
the empty field, or is that possible?


=DLookUp("myValue","myQuery"," [My_ID]=" & Forms![MyForm]!My_ID)

-S

 

[Scientific]

John, Douglas,

I found my answer looking at some different threads in this forum. Thank
you both for your reply.

-S

 

[Douglas J. Steele]

Are you saying Forms![MyForm]!My_ID is null?

You can either use the Nz function to use a value that doesn't exist in the
table:

=DLookUp("myValue","myQuery"," [My_ID]=" & Nz(Forms![MyForm]!My_ID, 0))

or you can use the IIf function to return something better:

=IIf(IsNull(Forms![MyForm]!My_ID), Null, DLookUp("myValue","myQuery","
[My_ID]=" & Forms![MyForm]!My_ID)

(both of those alternatives should be on a single line, of course...)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, John,

Sorry, I hit an errant key and posted prematurely.

I've been trying new ways to return data in a form control using a query
and
DLookUp. However, I noticed if you use DLookUp to search a field that's
null
you get an error that looks like "#Error". As an example, if I was using
the
code below in a control how could I use IIf to tell DLookUp not to search
on
the empty field, or is that possible?


=DLookUp("myValue","myQuery"," [My_ID]=" & Forms![MyForm]!My_ID)

-S

It's usually considered polite to put some context into the big white
space...

IIf(expr, truepart, falsepart)