multiple input parameters on a form

[Guest]

i have 4 input parameters on a form, and want the user to be able to enter
none, any, or all. i am trying to then build a single query to use whatever
parameters they entered (or did not enter) instead of the separate queries in
a conditional macro as i have now.

i am using Access 2003

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build the filter string for a form (or
WhereCondition for OpenReport) based on only the boxes where the user
entered something.

The end of the article also explains how this can be done in queries without
code, but that approach is rather messy.

 

[Guest]

gee, thanks! i had figured out the query option, but am anxious to try the
code. my application, however, uses an unbound form, and runs a query,
macro, or report depending on the selection a user chooses from a dropdown
box (after update). how would i produce the query results without attaching
it to a form's filter? thanks again.

 

[Allen Browne]

Simplest solution would be to build a form (in Datasheet view if you want it
to look like a query) to display the results.

You could create the whole SQL statement, and then assign it to the SQL
property of the querydef, e.g.:
Const strcStub = "SELECT * FROM Customers WHERE "
Const strcTail = " ORDER BY CustomerName;"
Dim strWhere As String
'Build the string as suggested in the article
CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail

 

[Whitney]

I tried to use your SQL, but no luck.

I have a form with the following filters
Start Date
End Date
Issue
Agent
SLRep

I would like the user to be able to enter a minimum of 1 criteria to all
criteria.

How do I code the query as well?