Query w/ User Input

  • Thread starter Thread starter Chris S.
  • Start date Start date
C

Chris S.

I am looking to create a form that will allow give a user
the flexibility to enter a single search item or multiple
items. The form would be flexible enought to let them
decide which fields to complete.

In my case, the user may have a very specific piece of
data that will return a single record, such as "apptmtID"
or they may only have more general information suchas a
person's name AND a range of appointment dates.

Any thoughts on how to approach this, especially regarding
passing the user entered data to the query.

Thanks.
 
When you have three or fewer criteria, you can use a parameter query that
performs the search like this:

WHERE ((SomeField = [Forms]![MyForm]![SomeFieldSearch]) Or
([Forms]![MyForm]![SomeFieldSearch] IS NULL))

If you have many search fields, using the above technique will often result
in "query too complex." The alternative is to use an unfiltered query as
the Record Source for a form or report that displays the result and then
build the predicate as a string in code behind the search form. Build
conditions only for criteria where the user has entered a value. Use the
predicate string as the WhereCondition parameter to an OpenForm or Open
Report. You can find examples of how to do the latter in the Books and
Entertain sample databases on my website.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top