IIF statement not behaving as intended in unbound form

[timmygrover]

The set up:



I'm working in a form that's not bound to a table or query. This issue is occurring in several parts of my form but I'll give one part as an example. Say we've got two unbound text boxes called [Fruit1] and [Fruit2] (say the user enters in Apples in one field and Bananas in the other), then we've got a third text box that outputs the following, "I like Apples and Bananas"

The statement:

I'm using an IIF statement to vary the output so that if one of the [Fruit] fields is left blank the sentence that outputs will still make sense (instead of getting "I like Apples and"). The statement, which goes into a 3rd text box looks like this:

=IIF(IsNull([Fruit2]), "I like " & [Fruit1], "I like " & [Fruit1] & " and " & [Fruit2])

I should note that [Fruit1] is a field that will be filled out every time, but [Fruit2] is optional

The issue:

The statement works perfectly if I type something into both fields, and then delete [fruit2], but if I start with a clear form and pass over [fruit2] altogether I get: "I like [Fruit1] and ". I've tried using the NZ() function in place of IsNull() and in that case the initial output is correct, but the statement doesn't update if I fill out [Fruit2].

The Question:

Why is this happening to me! /falltotheearth

How can I get my statement to work properly in this context?!

Thanks in advance for all your help!

[June7]

This could get tricky. Might want to build a custom function.

=IIf(IsNull([Fruit1]) and IsNull([Fruit2]), "I don't like fruit.", IIf(IsNull([Fruit1]), "I like " & [Fruit2], IIf(IsNull([Fruit2]), "I like " & [Fruit1], "I like " & [Fruit1] & " and " & [Fruit2])))

[timmygrover]

I've tried that actually (I did try June7's just to make sure) but the result is the same, "I like apples and". My musing has lead to the theory that since the form is unbound, and there is no table with a [fruit2] field, the form doesn't recognize it as being null until data has been entered and then removed. This is why i tried the NZ() function, but I can't get it to work out.

[June7]

Unbound form.

Unbound textboxes named Fruit1 and Fruit2.

Expression in textbox refers to Fruit1 and Fruit2 named textboxes.

As soon as data entered into Fruit1 and Fruit2 the expression will recalculate.

Tested. If this doesn't work for you, want to provide your project for analysis? Follow instructions at bottom of my post.

The textboxes must be null, not an empty string. The expression could be modified to deal with both null and empty string.

=IIf([Fruit1] & "" = "" And [Fruit2] & "" = "", "I don't like fruit.", IIf([Fruit1] & "" = "", "I like " & [Fruit2], IIf([Fruit2] & "" = "", "I like " & [Fruit1], "I like " & [Fruit1] & " and " & [Fruit2])))

[recyan]

perhaps,

=
IIf
    (
        Trim([txtFruit1]) Is Not Null,
        IIf
            (
                Trim([txtFruit2]) Is Not Null,
                "I like " & [txtFruit1] & " and " & [txtFruit2],
                "I like " & [txtFruit1]
        ),
        ""
)

Thanks

[June7]

I don't think Is Not Null works in IIf, at lease my test fails. It is SQL syntax. In IIf use: Not IsNull(txtFruit2)

[recyan]

Hi June,
Could you test it for me?
Assuming 2 unbound text boxes on the form [txtFruit1] & [txtFruit2],
&
a third text box with

=IIf(Trim([txtFruit1]) Is Not Null,IIf(Trim([txtFruit2]) Is Not Null,"I like " & [txtFruit1] & " and " & [txtFruit2],"I like " & [txtFruit1]),"")

Thanks

[June7]

I stand corrected. It works in textbox ControlSource. I was testing syntax in the VBA Immediate Window and that wouldn't work.

[recyan]

Hi,

Thanks a lot for the testing.

Thanks

[Missinglinq]

This will do what you want:

=IIf(IsNull([Fruit1]),"","I like " & [Fruit1] & " " & (" and "+[Fruit2]))

If Fruit1 is Null, it outputs nothing (actually, a Zero-Length String).

If Fruit1 is Not Null, it outputs "I like Fruit1"

The trick to this hack is that Anything plus a Null, is a Null, in other words, nada. So if Fruit2 is Null the "and" doesn't get output, either, and you don't end up with

"I like Fruit1 and"

Linq ;0)>

[timmygrover]

Everyone's solutions worked, thanks all for taking the time to help! In June7's solution though, what is the purpose of using & in the [Fruit1] & "" = "" part of the expression? What's the difference between & / AND?

[June7]

My second suggestion was handling possible null or empty string.

[Fruit1] & "" = ""

is an expression that concatenates an empty string to whatever [Fruit1] is, even null, and the result is a string which is then compared to the empty string entity. If still empty string after the concatenation, we know that no data was entered into the textbox. So regardless if null (and an unbound textbox should be null unless code sets it to an empty string) or empty string, the conditional IIf will work.

[timmygrover]

My second suggestion was handling possible null or empty string.

[Fruit1] & "" = ""

is an expression that concatenates an empty string to whatever [Fruit1] is, even null, and the result is a string which is then compared to the empty string entity. If still empty string after the concatenation, we know that no data was entered into the textbox. So regardless if null (and an unbound textbox should be null unless code sets it to an empty string) or empty string, the conditional IIf will work.

That makes sense, thanks so much for taking the time to explain