IIF IsNull help

[gymcshoe]

Hello all,

I'm trying to build a query using inputs from a form.

This is is the gist of what I'm trying to input into the expression builder

IIf(IsNull([Forms]![Form3]![product]),Is Not Null,[Forms]![Form3]![product])

Basically, the form has a combo box, that if a product is selected, then I
want all records that have that product to be shown. If the combo box is left
blank, then I want ALL records to be shown. How do I do this? Thanks

 

[Marshall Barton]

I'm trying to build a query using inputs from a form.

This is is the gist of what I'm trying to input into the expression builder

IIf(IsNull([Forms]![Form3]![product]),Is Not Null,[Forms]![Form3]![product])

Basically, the form has a combo box, that if a product is selected, then I
want all records that have that product to be shown. If the combo box is left
blank, then I want ALL records to be shown.

The criteria would be like:

=Forms!Form3!product OR Forms!Form3!product Is Null

 

[John Spencer]

Field: YourField
Criteria: [Forms]![Form3]![product] OR [Forms]![Form3]![product] is Null

After you close and reopen the query that will be reformatted by Access, but
it should still work.

If you get a query too complex error, then you will need to build the query
some other way.

You can simplify this if YOUR FIELD is never null and the field is a TEXT
field

Field: YourField
Criteria: LIKE Nz([Forms]![Form3]![product],"*")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[gymcshoe]

wow. Thanks a million, Marshall!. That works!

 

[gymcshoe]

Hello all,

I'm trying to build a query using inputs from a form.

This is is the gist of what I'm trying to input into the expression builder

IIf(IsNull([Forms]![Form3]![product]),Is Not Null,[Forms]![Form3]![product])

Basically, the form has a combo box, that if a product is selected, then I
want all records that have that product to be shown. If the combo box is left
blank, then I want ALL records to be shown. How do I do this? Thanks

ok guys, I have a similar issue

same scenario, I'm trying to build a query that pulls information from a form

On the form, there's a text box that I would like the user to type in "<3000"
for example. How do I build the query to pull this in as a criteria?

This alone does not work: [Forms]![Form3]![Revenue]

I get the this expression is typed incorrectly or is too complicated error.
Thanks again for the help

 

[gymcshoe]

Hello all,

[quoted text clipped - 7 lines]

want all records that have that product to be shown. If the combo box is left
blank, then I want ALL records to be shown. How do I do this? Thanks

ok guys, I have a similar issue

same scenario, I'm trying to build a query that pulls information from a form

On the form, there's a text box that I would like the user to type in "<3000"
for example. How do I build the query to pull this in as a criteria?

This alone does not work: [Forms]![Form3]![Revenue]

I get the this expression is typed incorrectly or is too complicated error.
Thanks again for the help

Sorry let me clarify, If the user types in "<3000" into the revenue text box,
I want all records that have less than 3000 in revenue to be shown. If the
user does not type in anything, then I want all records to be shown. Thanks

 

[John Spencer]

You have a response to this question in
Microsoft.Public.access.gettingstarted.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello all,

[quoted text clipped - 7 lines]

want all records that have that product to be shown. If the combo box is
left
blank, then I want ALL records to be shown. How do I do this? Thanks

ok guys, I have a similar issue

same scenario, I'm trying to build a query that pulls information from a
form

On the form, there's a text box that I would like the user to type in
"<3000"
for example. How do I build the query to pull this in as a criteria?

This alone does not work: [Forms]![Form3]![Revenue]

I get the this expression is typed incorrectly or is too complicated
error.
Thanks again for the help

Sorry let me clarify, If the user types in "<3000" into the revenue text
box,
I want all records that have less than 3000 in revenue to be shown. If the
user does not type in anything, then I want all records to be shown.
Thanks