Need to cancel parameter criteria if prompt is blank

[alekfelstiner]

I've built a search form with abou 15 controls, and I want users to be able
to select parameter criteria freely. If they leave a control blank, I don't
want to lose all the records in the resultant query, NOR do I want the query
to return every record.

What I need is something that will insert criteria into the query from the
form if a user select/enters it, and otherwise treats the field as if the
criteria was blank.

Is that possible?

 

[Amy Blankenship]

I've built a search form with abou 15 controls, and I want users to be
able
to select parameter criteria freely. If they leave a control blank, I
don't
want to lose all the records in the resultant query, NOR do I want the
query
to return every record.

What I need is something that will insert criteria into the query from the
form if a user select/enters it, and otherwise treats the field as if the
criteria was blank.

If the fields you need to search on will always contain a non-null value,
you can use something like:

Like '*' & [Enter a value] & '*'

For fields that could contain a null value, you need to treat [Enter a
value] as a field and then specify what happens when it is null, when it is
not null, when each value is null, and when each value is not null, using
the OR lines. That means the Cartesian set of your various fields could
potentially get quite large. I would not suggest trying it with above 5
fields that have the potential to contain null values.

HTH;

Amy

 

[Ken Sheridan]

Test each parameter for IS NULL within parenthesised Boolean OR operations,
e.g.

SELECT *
FROM MyTable
WHERE
(Field1 = Forms!MyForm!Control1)
OR Forms!MyForm!Control1 IS NULL)
AND
(Field2 = Forms!MyForm!Control2)
OR Forms!MyForm!Control2 IS NULL)
AND
(Field3 = Forms!MyForm!Control3)
OR Forms!MyForm!Control3 IS NULL)
<and so on>
AND
(Field15 = Forms!MyForm!Control15)
OR Forms!MyForm!Control15 IS NULL);

If a control is left blank (NULL) then the relevant parenthesised expression
will evaluate to TRUE, so the result set will not be restricted on that
column, but, by virtue of the Boolean AND operations, only on those columns
whose parameters have values, i.e those in which a value has been
entered/selected in the form.

You'll find this much easier to do in SQL view than in query design view.
If you then subsequently save the query in design view, however, the next
time you open it in design view you'll find that Access has moved things
around a lot. It will work exactly the same however, but for readability of
the query you might prefer to save it in SQL view before Access has a chance
to shift things about.

Ken Sheridan
Stafford, England

 

[alekfelstiner]

Ken -

This still returned all the records, I think because it allows both null and
not null results for each parameter -- so the AND limitation doesn't limit
anything. Do you have a suggestion for how to fix that?

Also, Access demanded that I remove the parentheses at the end of NULL --
that wouldn't have anything to do with it, right?

Thanks

Alek

 

[Ken Sheridan]

Mea culpa; an extra closing parenthesis somehow crept in. It should have read:

SELECT *
FROM MyTable
WHERE
(Field1 = Forms!MyForm!Control1
OR Forms!MyForm!Control1 IS NULL)
AND
(Field2 = Forms!MyForm!Control2
OR Forms!MyForm!Control2 IS NULL)
AND
(Field3 = Forms!MyForm!Control3
OR Forms!MyForm!Control3 IS NULL)
<and so on>
AND
(Field15 = Forms!MyForm!Control15
OR Forms!MyForm!Control15 IS NULL);

Ken Sheridan
Stafford, England

 

[Amy Blankenship]

Ken -

This still returned all the records, I think because it allows both null
and
not null results for each parameter -- so the AND limitation doesn't limit
anything. Do you have a suggestion for how to fix that?

Also, Access demanded that I remove the parentheses at the end of NULL --
that wouldn't have anything to do with it, right?

Thanks

Alek

You need to have separate Or lines that determine what happens with all
combinations of the parameters being left null vs. not being left null,
except in cases where the field itself can never be null, in which case you
can use something like LIKE [Enter a value] & '*'.

http://support.microsoft.com/kb/290178/

-Amy