Re: query based on form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Leo Bonnaci" <always-learning@xxxxxxxxxxxx> wrote in message
news:ORBxs0l8IHA.4108@xxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: Whats wrong?
    ... variables into the SQL statement? ... That way, as the cursor is declared only once, and even if you change the query inside the statement variable, each time you open the cursor the first query will be executed, that's not what I need. ... The USING clause should be specific for opening the cursor with a changed query each time. ...
    (comp.sys.ibm.as400.misc)
  • Re: Ranking my results in a query to get the TOP#10 percent
    ... Without order by clause, it just returns 10 percent of the records. ... Can this be accomplished in same Total query where I perform a "COUNT" on ... the SQL statement suggested below. ... Could you please assist me with an Access SQL statement that can ...
    (microsoft.public.access.queries)
  • Re: Send filtered subform data to excel
    ... Private Sub Export_to_Excel_Click ... A QueryDef is just a saved query (i.e. the definition of the of the query, ... It is defined as a SQL statement, ... WHERE clause), goes in the constant we named strcTail. ...
    (microsoft.public.access.formscoding)
  • Re: Optimisation of a sql query
    ... About using group by and having, I have to confess that the query I ... Shall I go ahead with the where clause as suggested ... passed on to the grouping (and possible sorting operation for Oracle ... you can start tuning the SQL statement. ...
    (comp.databases.oracle.server)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)