querydef and the DoCmd.OutputTo method

[Guest]

Greetings,

I have several reports that are based upon a couple of large tables. I have
it set up to where the DB user has lots of filtering options and I build SQL
WHERE clauses to filer the reports… and this works fine. I also have it so
that the user can export the reports to a file as various formats. Here
though, I use the “DoCmd.OutputTo†method and there is not place for a WHERE
clause. I have gotten around this by using the

CurrentDb.QueryDefs("qrySharedMemberData02").SQL = strSQL1

for the report query. Now that works… but strSQL is massive because it
contains all the query defs and this is a big query. My question: Is there
a simple way to filter an existing query by applying the WHERE statement
without having to define all the query fields every time? Bruit force works,
but I would like to find a simpler way.

Thanks for your help.

 

[Allen Browne]

Re-writing the SQL property of the QueryDef is probably the easiest way.

Your code can hold the stub of the SQL statement in a contstant (typically
the SELECT and FROM clauses), and the tail in another constant (typically
the ORDER BY clause), so it builds the WHERE clause and patches them
together. This kind of thing:
Dim strWhere As String
Const strcStub = "SELECT Table1.* " & vbCrLf & _
"FROM Table1 " & vbCrLf &
"WHERE ("
Const strcTail = ") " & vbCrLf & "ORDER BY ID;"