Get an iif statemnt to search for a null value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Julianne - 14 Dec 2005 20:48 GMT

You all have been so wonderful and extremely helpful. May I borrow upon your
expertise again? In a report I would like to create a text box that checks
to see if a field has a null value. I've been using the Iif function but am
at a loss as how to ask it to "check to see if the field is blank. If it is,
return the word "problem." Thank you again and again for sharing your
knowledge. Happy Holidays and a joyous New Year.

Reply to this Message

George Nicholson - 14 Dec 2005 21:14 GMT

Null never "equals" anything, so Anything = Null will always return False.

Paraphrasing Yoda: "Null Is, or Is Not. There is no 'equal'."

ahem.

Try:
= iif(IsNull([MyField], "Problem", [MyField])

or, use Access' (not VB's) nz (NullToZero) function to achieve the same
thing with less typing:
= nz([MyField],"Problem")

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

> You all have been so wonderful and extremely helpful. May I borrow upon
> your
[quoted text clipped - 6 lines]
> return the word "problem." Thank you again and again for sharing your
> knowledge. Happy Holidays and a joyous New Year.

Reply to this Message

Marshall Barton - 14 Dec 2005 21:25 GMT

>In a report I would like to create a text box that checks
>to see if a field has a null value. I've been using the Iif function but am
>at a loss as how to ask it to "check to see if the field is blank. If it is,
>return the word "problem."

A text box expression could use:
    =Nz(thefield, "Problem")

Or, in more complex situations:
    =IIf(thefield Is Null, "Problem", something else)

OTOH, in a VBA statement you would use:
    ... IIf(IsNull(thefield), "Problem", something else) ...

Signature

Marsh
MVP [MS Access]

Reply to this Message