query based on form

[Leo Bonnaci]

I want to create a query (that exports to Excel -I can do the export part),
based on the user imput from a form.

The are 8 different fields to choose from (ie. renewal month, city, county,
state, Sales Rep, type of business, etc.). I want the user to have a combo
box for each of these fields to choose one record type or all records.

Example: they can choose one Sales Rep, all cities, all states, all
counties, just one type of business and one renewal month. Then run it
again, and this time choose all Sales Reps, a single county, all states, and
one type of business and all months. Etc., Etc., Etc.

I know I could create hundreds of queries for each combination and write
code for each possibility but thought that there must be a better way. Do I
do it through the query or through VBA code on the form (or combination of
both)?

Thanks,
Leo

 

[Allen Browne]

Typically it's only the WHERE clause of the query that changes so you can
build that dynamically, patch it into the SQL statement, and assign it to
the SQL property of the QueryDef you use for export.

This is the basic idea:
Const strcStub = "SELECT * FROM Table1 " & vbCrLf & " WHERE "
Const strcTail = " ORDER BY Field1;" & vbCrLf
Dim strWhere As String
strWhere = "...
CurrentDb.QueryDefs("MyExportQuery").SQL = strcStub & strWhere & strcTail

You may already know how to build the WHERE clause from lots of filter
boxes, but if you want an example, download this one:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Leo Bonnaci]

Allen,

Thanks that is real helpful. I have not done a lot with SQL statements. This
will be a good learning experience.

Leo