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
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.