Null Criteria Problem


A

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.
 
Ad

Advertisements

J

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")
 
A

AJ

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


John W. Vinson said:
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")
 
A

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


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


John W. Vinson said:
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")
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top