How To Specify No Criteria

  • Thread starter Thread starter Bobmeister
  • Start date Start date
B

Bobmeister

I am using a form to enable a user to specify criteria for a query. On this
form there are several fields that the user can fill in. I am using these
fields as And logic requiring all criteria to be met for a record to be
returned. In the Query, where I specify the criteria on which to filter the
data, I point to combo boxes in the form using this format which works when
all fields are filled out.

[Forms]![frmIndividualSearch]![cmdWho]

However if I don't fill in one of the fields in the form, then I always get
a Null result of no records from the query.

If I use fields in a form to specify criteria, how do I get the query to
ignore an empty entry when I do not fill in a field among many on the form?
Any insights on this would be appreciated.

I have unsuccessfully tried entries like:

IIf([Forms]![frmIndividualSearch]![cmdWho]=0,([ExecutedDocumentList].[IndividualID]) Like "*",[Forms]![frmIndividualSearch]![cmdWho])
 
The simplest answer is that you need to craft the SQL statement of the query
so that the expression evaluates to TRUE if the control on the form is null.

So, when you switch the query to SQL View, you see this kind of thing:
WHERE (([Forms]![frmIndividualSearch]![cmdWho] Is Null) OR
([Forms]![frmIndividualSearch]![cmdWho] =
[ExecutedDocumentList].[IndividualID]))
AND ...

Note that this is not the same as pasting into the Criteria in query design
view. Also, watch the bracketing when mixing ANDs and ORs.

In practice, this becomes quickly becomes unwieldy to manage and inefficient
to execute when you have several optional criteria. You are far better off
to omit the criteria from the query, and build a filter string from only the
boxes on the form where the user actually put some criteria.

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The link includes a free downloadable example for you to pull apart and see
how it works.

It's really worth the effort to learn that approach. You will use it not
only for searching and filtering in forms, but also for interfacing your
reports (offering lots of filtering options.)
 
Your WHERE CLAUSE might look like:

WHERE IndividualID = [Forms]![frmIndividualSearch]![cmdWho] OR
[Forms]![frmIndividualSearch]![cmdWho] Is Null

If the query is used as the record source of a form or report, I generally
build a WHERE CONDITION in code to use in the DoCmd.Open... method.
 
Back
Top