ACC: Exporting a Parameter Query to a Spreadsheet or Text File
ID: Q124514
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
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:
Macro Name Action
--------------------------------
Macro1 TransferSpreadsheet
Macro1 Action
--------------------------------
TransferSpreadsheet
Transfer Type: Export
Table Name: Query1
File Name: C:\access\test.xls
- Save the macro as Macro1 and then close it.
- Select the Macro1 macro in the Database window and then choose the
Run button.
- 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.
REFERENCES
For more information about the TransferText macro action, search for
"TransferText," and then "TransferText Action" using the Microsoft Access
Help menu.
For more information about the TransferSpreadsheet macro action, search for
"TransferSpreadsheet," and then "TransferSpreadsheet Action" using the
Microsoft Access Help menu.
Keywords : kberrmsg kbusage QryParm
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbinfo