ACC: "Too few parameters" When Using Microsoft Access Data in Microsoft Query (171850)

SYMPTOMS

When you create a parameter query based on a Microsoft Access database in Microsoft Query, you may receive the following error message:

Too few parameters. Expected <n>.

If you click Help in the error message box, the topic states,

   This error was returned by the driver. For more information, check the
   Documentation for the driver you are currently using.

This error can occur under the following circumstances:

You are creating your parameter query in Microsoft Query version 2.0.
You are using either Microsoft Query version 2.0 or version 8.0 to make a parameter query against data from a Microsoft Access 7.0 database.
You are using Microsoft Query version 8.0 to make a parameter query against a Microsoft Access 97 query, and specifying your criteria on a column that uses the Format method to format the output.

NOTE: This behavior also occurs using the Mail Merge Wizard to merge a Word 95 document with a parameter query in Microsoft Access 95.

WORKAROUND

Parameter Specified in Microsoft Query Version 2.0

Although the prompt for criteria is not available in Microsoft Query version 2.0, you can click Criteria on the View menu, and then change the contents of the Criteria field's Value row to view different query results.

Parameter Specified in Microsoft Access

In Microsoft Access, create a make-table query based on the parameter query; run the make-table query, and then use the resulting table in Microsoft Query.

If you are using Microsoft Query to make the results of a Microsoft Access parameter query available to Microsoft Excel, you may want to consider exporting the parameter query to a Microsoft Excel file from within Microsoft Access.

MORE INFORMATION

Steps to Reproduce Behavior

Example 1: Parameter Query Created in Microsoft Query

Start Microsoft Query version 2.0. On the File menu, click New Query.
Select MS Access 7.0 Database or MS Access 97 Database in the Select Data Source dialog box and click Use.
In the Select Database dialog box, locate the sample database Northwind.mdb, select it, and then click OK.
Select Current Product List in the Add Tables dialog box and click Add; then, click Close.
Add the ProductName field to the query.
On the View menu, click Criteria.
Select ProductName from the list in the Criteria field and type [Enter a Product Name] in the Value row.
On the Records menu, click Query Now.

You receive the following error message:
Too few parameters. Expected 1.

NOTE: This behavior does not occur in Microsoft Query 97.

Example 2: Parameter Query Created in Microsoft Access

Start Microsoft Access and open the sample database Northwind.mdb.
Open the Current Product List query in Design view.
On the Criteria line of the ProductName field type [Enter a Product Name].
Save and close the query; quit Microsoft Access.
Start Microsoft Query.
On the File menu, click New (or New Query in Microsoft Query version 2.0).
If you are using Microsoft Query version 8.0, select MS Access 7.0 Database or MS Access 97 Database in the Choose Data Source dialog box, and then click OK.

If you are using Microsoft Query version 2.0, select MS Access 7.0 Database or MS Access 97 Database in the Select Data Source dialog box, and then click Use.
In the Select Database dialog box, locate the sample database Northwind.mdb, select it, and then click OK.
Select Current Product List. If you are using Microsoft Query version 8.0, click Next twice, and then click Finish.

If you are using Microsoft Query version 2.0, click Add in the Add Tables dialog box and click Close; then add the field ProductName to the query.

You receive the error message:
Too few parameters. Expected 1.

NOTE: This behavior occurs in all versions of Microsoft Query.

REFERENCES

For more information about parameters, search the Microsoft Access Help Index for "parameter queries."

For more information about make-table queries, search the Microsoft Access Help Index for "make-table queries, creating," and then display the topic "Create a new table from the results of a query with a make-table query."

For more information about exporting data from Microsoft Access to Microsoft Excel, search the Microsoft Access Help Index for "Excel, exporting Microsoft Access data to Microsoft Excel," and then display the topic "Export a table or query to a spreadsheet."