Efficiently Export Data with varying selection criterion

A client has requested an export similar to a report they are not generating in the system.  The report has a selections screen populated with 7 text boxes (payment source, payment date range, payment status, etc.....)that allow them to specify what information appears on the report.  None of the text boxes are required entry.  Based on what was entered on the forms I am currently building a where clause(gRptWhereCond).  The where clause is used in the report and there is logic in the report 'open'  to chnage the recordsource to include the 'Where clause built from what was entered by the user.  The lofgic in the Report_Open looks like this:

    strNewRecord = "SELECT * FROM qryRPT_PaymentsByDepositNumber " & gRptWhereCond
    Me.RecordSource = strNewRecord

The logic to build the Where Clause looks at each filed on the input form to determine if any entry was made.  If a particular text box was entered a string is appended to the where clause with the additional criterion.  The end result is a where clause containing only the criterion entered by the user.

For a text box such as 'Payment Source', if nothing is entered all payment sources are included.

It all works fine.

Now the user wants the option to be able to export the selected information instead of creating a hard copy report.

Since the contents of the where clause is always going to be dynamic here is how I plan to accomplish this.  

I follow the same logic to build the where clause.
I then insert all of the qualifying records into a table with

insertString = "Insert Into tblWorkTable " & _
                           " Select *   " & _
              " From qryRPT_PaymentsByDepositNumber " & gRptWhereCond
DoCmd.RunSQL insertString

At that point only the selected records will be in tblWorkTable and I will use

DoCmd.TransferText acExportDelim, , tblWorkTable, wkExportNameAndLoc, True

to generate the output file.

It seems incredibly wasteful to have to create  tblWorkTable just to get a filtered set of data that can be exported using the docmd.transfertext command.  I tried putting a SQL statement in the place of 'tblWorkTable' in the above example but it isn't accepted.

Is there a more direct way of doing this without having to create an interim work table?  I have heard of paramertized queries but they seem to require a fixed set of parameters, meaning all parameters must be passed.  That is not the case in this situation.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
You could create an exportquery and save it, then modify the SQL of the query to something like:

Dim qdf as dao.querydef
set qdf = currentdb.QueryDefs("qryExport")
qdf.SQL = currentdb.QueryDefs("qryYourReport") & strWHERE
set qdf = nothing

Then use qryExport as the datasource for your TransferText or TransferSpreadsheet operation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
So the original definition of qryExport is not important since you would always be overlaying it with what you really want to be in there.  

Since my query is called 'qryRPT_PaymentsByDepositNumber' and my where clause is built in 'gRptWhereCond', In my case the above would become

Dim qdf as dao.querydef
 set qdf = currentdb.QueryDefs("qryExport")
 qdf.SQL = currentdb.QueryDefs("qryRPT_PaymentsByDepositNumber") & gRptWhereCond
 set qdf = nothing

DoCmd.TransferText acExportDelim, , qryExport, wkExportNameAndLoc, True

Is this correct?
mlcktmguyAuthor Commented:
I entered:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryExport_GeneralUse")
qdf.SQL = CurrentDb.QueryDefs("qryRPT_PaymentsByDepositNumber") & gRptWhereCond
Set qdf = Nothing

I'm getting a Compiler error 'Type Mismatch'  and '.QueryDefs' is highlighted on this line

qdf.SQL = CurrentDb.QueryDefs("qryRPT_PaymentsByDepositNumber") & gRptWhereCond
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dale FyeOwner, Developing Solutions LLCCommented:
Sorry, that should read:

qdf.SQL = CurrentDb.QueryDefs("qryRPT_PaymentsByDepositNumber").SQL & gRptWhereCond

Just for grins, you might want to add:

debug.print qdf.SQL

after the previous line
mlcktmguyAuthor Commented:
With the new code I was getting another error "Characters at End of SQL' on the line

qdf.SQL = CurrentDb.QueryDefs("qryRPT_PaymentsByDepositNumber").SQL & gRptWhereCond

at time of execution.

Seems 'CurrentDb.QueryDefs("qryRPT_PaymentsByDepositNumber").SQL' had a semicolon at the end.  I put in logic that removed the last semicolon before appending 'gRptWhereCond' and it worked perfectly.
mlcktmguyAuthor Commented:
Exactly what I was looking for.
Dale FyeOwner, Developing Solutions LLCCommented:
Glad to help.  

Sorry about the semi-colon, one of the reasons for the debug.print line!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.