Multiple Criteria Search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form where I have a search set up by 5 different criteria using
combo boxes. The search works when I use all five criteria. Is there a way
to set it up so where I could search by using only some of the 5 differerent
criteria set up?

It's a database for a staffing agency doing searches for potentials matches
to job openings. Criteria's are: Job Title, Years Experience, Education,
PMI Certified(Y/N), and Mortgage Experience(Y/N)It's a database for a
staffing agency doing searches for potentials matches to job openings. May
need to do some searches by just Job Title & Yrs Experience while other may
need Job Title, Yrs Experience and if they have mortgage experience.
 
There are a number of ways you could approach this. One would be to examine
the 5 controls in the form in code and build an expression based on the
non-Null controls only, which can then be used as the WhereCondition argument
of the OpenForm or OpenReport method.

A simpler approach would be to reference the 5 controls as parameters in a
query which is used as the RecordSource for a form or report. To make the
parameters optional test each for NULL in a Boolean OR operation like so:

SELECT *
FROM Clients
WHERE
([Job Title] = Forms![YourForm]![JobTitle]
OR Forms![YourForm]![JobTitle] IS NULL)
AND
([Years Experience] = Forms![YourForm]![ Years Experience]
OR Forms![YourForm]![ Years Experience] IS NULL)
AND
([Education] = Forms![YourForm]![ Education]
OR Forms![YourForm]![ Education] IS NULL)
AND
([PMI Certified] = Forms![YourForm]![PMI Certified]
OR NOT Forms![YourForm]![PMI Certified])
AND
([Mortgage Experience] = Forms![YourForm]![ Mortgage Experience]
OR NOT Forms![YourForm]![ Mortgage Experience]);

Note how each Boolean OR operation is parenthesised to force it to evaluate
independently of the Boolean AND operations.

Note also that the Boolean (Yes/No) columns PMI Certified and Mortgage
Experience are handled slightly differently. I'm assuming you have a check
box for the criteria for each of these, and that it cannot be Null (give each
check box a default value of False). In this case the OR operation evaluates
to TRUE if the value of the column for a row in the table matches the check
box's value, so if the check box is True (checked) then only rows with column
value of TRUE would cause the parenthesised expression to evaluate to TRUE.
If the checkbox is FALSE then the expression will evaluate to TRUE regardless
of the column value for a row. This only allows you to select a positive
criterion for each of these two columns, not a negative one, e.g. you can
select clients WITH mortgage experience or clients with or without mortgage
experience (by leaving the check box unchecked) but you can't select clients
without mortgage experience, excluding those with it. I'm assuming that's
how you'd want it to work.

By virtue of the Boolean AND operations a row is returned by the query only
where all of the parenthesised Boolean expressions evaluate to TRUE.

If the form is a dialogue form from which a form or report based on the
query is opened then all you have to do is open the form or report with a
button on the dialogue form. If the unbound controls are on the form bound
to the query then you just have to requery the form with code in the button's
Click event procedure:

Me.Requery

Ken Sheridan
Stafford, England
 
Back
Top