Null Criteria Problem

[AJ]

Thanks to previous threads I'm this far, but now stuck...

I have a form = MBRform

I have a text box ([text241] with an iff statement based upon another field.
iif([text181]="xxx", "abc","is null")

Then I have a query
Field1
Criteria = =forms!mbrform!text241 or forms!mbrform!text241 is null

FIeld2 = forms!mbrform!text188

The qry pulls just fine when the iff statement is true. But, when the iff
statement is false I get no results at all.

Thanks in advance for any help.

 

[John W. Vinson]

Thanks to previous threads I'm this far, but now stuck...

I have a form = MBRform

I have a text box ([text241] with an iff statement based upon another field.
iif([text181]="xxx", "abc","is null")

That won't help. You're mixing text with values - your query is searching for
the literal text string "is null".

Then I have a query
Field1
Criteria = =forms!mbrform!text241 or forms!mbrform!text241 is null

Try changing the criterion to

=("abc" AND [Forms]![mbrform]![text141] = "xxx") OR (Is Null AND
[Forms]![mbrform]![text141] <> "xxx")

 

[AJ]

Thanks so much, however, it gives an error that the expression is too complex.
--
AJ

Thanks to previous threads I'm this far, but now stuck...

I have a form = MBRform

I have a text box ([text241] with an iff statement based upon another field.
iif([text181]="xxx", "abc","is null")

That won't help. You're mixing text with values - your query is searching for
the literal text string "is null".

Then I have a query
Field1
Criteria = =forms!mbrform!text241 or forms!mbrform!text241 is null

Try changing the criterion to

=("abc" AND [Forms]![mbrform]![text141] = "xxx") OR (Is Null AND
[Forms]![mbrform]![text141] <> "xxx")

 

[AJ]

I have answered my own question. Thanks John, you did point me in the right
direction.

I made the text box on the form text241=iif(text181="xxx","abc","")

Then in the qry
Field1
Criteria like form!mbrform!text241 & "*"

Field 2 criteria I didn't change.

Everything works great with that.

Thanks again
--
AJ

Thanks so much, however, it gives an error that the expression is too complex.
--
AJ

Thanks to previous threads I'm this far, but now stuck...

I have a form = MBRform

I have a text box ([text241] with an iff statement based upon another field.
iif([text181]="xxx", "abc","is null")

That won't help. You're mixing text with values - your query is searching for
the literal text string "is null".

Then I have a query
Field1
Criteria = =forms!mbrform!text241 or forms!mbrform!text241 is null

Try changing the criterion to

=("abc" AND [Forms]![mbrform]![text141] = "xxx") OR (Is Null AND
[Forms]![mbrform]![text141] <> "xxx")

 

[John W. Vinson]

Thanks so much, however, it gives an error that the expression is too complex.

Please post the complete SQL string.