Microsoft KB Archive/124514

= ACC: Exporting a Parameter Query to a Spreadsheet or Text File =

Article ID: 124514

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q124514



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
NOTE: The problem described below does not occur in Microsoft Access version 7.0. In version 7.0, exporting a parameter query causes the query to prompt for input in the same way it normally would.

This article describes how to use the TransferText or TransferSpreadsheet action in a macro to export a parameter query. (A parameter query is a query that requires input in order to run.) You can export queries without parameters using the TransferText or TransferSpreadsheet macro action using the query's name for the action's Table Name argument. If you try to do this with a parameter query, however, you receive the following error message:

1 parameters were expected, but only 0 were supplied.

To export a parameter query, you can eliminate the parameter from the query and instead pass it using an Access Basic function called in the query's Criteria row.



MORE INFORMATION
The following example demonstrates how to export a parameter query:

 Open the sample database NWIND.MDB. Create a new query based on the Customers table. Drag the Company Name and the City fields from the field list to the query grid. In the Criteria row for the City column, type Find_City. Save the query as Query1 and then close it.  Create a new module and then enter the following sample code in the module:

Function Find_City

Find_City = Inputbox("Enter city name") ' You could also use "= [Forms]![Customers]![City]" with the ' assumption that the Customers form is open and that the City ' field contains a value. End Function  Save the module as Module1 and then close it.  Create the following new macro:

<pre class="fixed_text">     Macro Name   Action Macro1      TransferSpreadsheet

Macro1 Action TransferSpreadsheet Transfer Type: Export Table Name: Query1 File Name: C:\access\test.xls </li> Save the macro as Macro1 and then close it.</li> Select the Macro1 macro in the Database window and then choose the Run button.</li> When you are prompted "Enter city name," enter London and then choose the OK button. When you choose OK, a spreadsheet file called TEST.XLS is created in the ACCESS directory on drive C. It contains only records with a city of London.</li></ol>

<div class="references_section">