If null then no criteria

[Andrew P.]

Hi All

Hope someone can help with this one. I want to set up a query to run from a
bunch of combo boxes. If the user selects none, then all records should be
shown. If the user selects one combo or more, then I want the query to kick
in. Ive come up with a few different variations, none of which work because:
access complains that its too complet, wrong syntax, or no results are
returned. They all look a bit like this:

IIf(IsNull([Forms]![ThisWeek_2-Select]![Combo1]),"*",[Forms]![ThisWeek_2-Select]![Combo1])

Thanks a lot
Andrew

 

[Jeanette Cunningham]

Hi Andrew,
there is a sample search database on
http://allenbrowne.com/ser-62.html

that shows how to use unbound combos to build a filter string to filter a
form.
You could change this example to build a query instead of a filter.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

Hi All

Hope someone can help with this one. I want to set up a query to run from a
bunch of combo boxes. If the user selects none, then all records should be
shown. If the user selects one combo or more, then I want the query to kick
in. Ive come up with a few different variations, none of which work because:
access complains that its too complet, wrong syntax, or no results are
returned. They all look a bit like this:

IIf(IsNull([Forms]![ThisWeek_2-Select]![Combo1]),"*",[Forms]![ThisWeek_2-Select]![Combo1])

Try instead:

= [Forms]![ThisWeek_2-Select]![Combo1] OR [Forms]![ThisWeek_2-Select]![Combo1]
IS NULL

 

[Andrew P.]

= [Forms]![ThisWeek_2-Select]![Combo1] OR
[Forms]![ThisWeek_2-Select]![Combo1] IS NULL

Brilliant! Thanks a lot John. I don't quite understand how that statement
works to negate the criteria. Could you please explain it?

Thanks again
Andrew

 

[Andrew P.]

Thanks Jeanette, but that one is just beyond me.

Hi Andrew,
there is a sample search database on
http://allenbrowne.com/ser-62.html

that shows how to use unbound combos to build a filter string to filter a
form.
You could change this example to build a query instead of a filter.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi All

Hope someone can help with this one. I want to set up a query to run from
a
bunch of combo boxes. If the user selects none, then all records should be
shown. If the user selects one combo or more, then I want the query to
kick
in. Ive come up with a few different variations, none of which work
because:
access complains that its too complet, wrong syntax, or no results are
returned. They all look a bit like this:

IIf(IsNull([Forms]![ThisWeek_2-Select]![Combo1]),"*",[Forms]![ThisWeek_2-Select]![Combo1])

Thanks a lot
Andrew

 

[John W. Vinson]

= [Forms]![ThisWeek_2-Select]![Combo1] OR

[Forms]![ThisWeek_2-Select]![Combo1] IS NULL

Brilliant! Thanks a lot John. I don't quite understand how that statement
works to negate the criteria. Could you please explain it?

Thanks again
Andrew

The way a criterion works is: Access evalulates it as a logical expression. If
the expression is TRUE, the record is retrieved; if the expression is FALSE,
it is not.

So it's looking at two possible expressions:

[fieldname] = [Forms]![ThisWeek_2-Select]![Combo1]

This expression might or might not be true if there is some value in Combo1;
if Combo1 is empty, though, it is certainly not TRUE (in fact, it's null, and
will be treated as if it were False).

The other expression is

[Forms]![ThisWeek_2-Select]![Combo1] IS NULL

This expression will be false if there is any value in Combo1, and True if
combo1 is empty.

So... if combo1 is NOT empty, it evaluates the first expression (which might
be true or false for any given record).
If combo1 IS empty, the second expression will be TRUE.

Using OR will return TRUE if either the first or the second expression is
TRUE; so if the combo is left empty, the combined expression will be TRUE
regardless of the contents of the field.

 

[Andrew P.]

Thanks a lot John!