Nick said:
I have a form that allows the user to search on a particular
attribute of a person: ie surname, forename, DOB etc. by selecting
from a drop down menu. The search criteria is then input into a
textbox, and then a query is run; ie select "Surname" from ddm and
type "Jones", the query "qry_surname" is run searching for "Jones".
Selecting a different attribute runs separate query. This is an
extremely crude way of doing it, and I have about 8 attributes
therefore 8 different queries. Is there a way of combining this
search into one query?
Help greatly appreciated
Only if you are willing to re-write the SQL of the query each time in code or
build a pretty convoluted query. Parameters pulled from form references can
only choose your criteria value, not which field the criteria is applied to.
You would have to write the query like...
SELECT * FROM TableName
WHERE (Surname = Forms!FormName!TextBoxName
OR Forms!FormName!ComboBoxName <> "Surname")
AND (Forename = Forms!FormName!TextBoxName
OR Forms!FormName!ComboBoxName <> "Forename")
If you look at the example the query will look for Surnames matching what the
user types into the TextBox, but only when the ComboBox has a value of "Surname"
and will also look for Forenames matching what the user typed IF the value in
the ComboBox is "Forename". As you can imagine such a query will get quite
complex as the number of attributes grows.
What do you ultimately do with the result of the search? Open a form or report
showing the matching records? If so, what I would do is create a form/report
that shows all records by default and then I would use code to build a WHERE
clause based on the user's search criteria and apply that in the OpenForm or
OpenReport method...
Dim filterCriteria as String
filterCriteria = Me.ComboBoxName & " = '" & Me.TextBoxName & "'"
DoCmd.OpenForm "FormName",,,filterCriteria
Now, that is an ovelry simplified example because it assumes that all fields
being filtered on are text fields. If you have a mixture of Text, Numeric, and
DateTime fields then you will have to test the value in the ComboBox and choose
the appropriate delimiters to use around the TextBox value (quotes for Strings,
# for DateTimes, and none for Numbers).
