• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

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.
0
mlcktmguy
Asked:
mlcktmguy
  • 4
  • 3
2 Solutions
 
Dale FyeCommented:
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.
0
 
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?
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
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
0
 
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.
0
 
mlcktmguyAuthor Commented:
Exactly what I was looking for.
0
 
Dale FyeCommented:
Glad to help.  

Sorry about the semi-colon, one of the reasons for the debug.print line!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now