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

This article was previously published under Q171850
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you create a parameter query based on a Microsoft Access database inMicrosoft 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 Word95 document with a parameter query in Microsoft Access 95.
CAUSE
This is a limitation of the Microsoft Access ODBC driver. Prompting forquery parameters is a functionality of the Microsoft Access user interface.However, the Microsoft Access ODBC driver doesn't have this capability;therefore, it generates an error message.
WORKAROUND

Parameter Specified in Microsoft Query Version 2.0

Although the prompt for criteria is not available in Microsoft Queryversion 2.0, you can click Criteria on the View menu, and then change thecontents 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 parameterquery; run the make-table query, and then use the resulting table inMicrosoft Query.

If you are using Microsoft Query to make the results of a Microsoft Accessparameter query available to Microsoft Excel, you may want to considerexporting the parameter query to a Microsoft Excel file from withinMicrosoft Access.
MORE INFORMATION

Steps to Reproduce Behavior

Example 1: Parameter Query Created in Microsoft Query


  1. Start Microsoft Query version 2.0. On the File menu, click New Query.
  2. Select MS Access 7.0 Database or MS Access 97 Database in the Select Data Source dialog box and click Use.
  3. In the Select Database dialog box, locate the sample database Northwind.mdb, select it, and then click OK.
  4. Select Current Product List in the Add Tables dialog box and click Add; then, click Close.
  5. Add the ProductName field to the query.
  6. On the View menu, click Criteria.
  7. Select ProductName from the list in the Criteria field and type [Enter a Product Name] in the Value row.
  8. 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


  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Open the Current Product List query in Design view.
  3. On the Criteria line of the ProductName field type [Enter a Product Name].
  4. Save and close the query; quit Microsoft Access.
  5. Start Microsoft Query.
  6. On the File menu, click New (or New Query in Microsoft Query version 2.0).
  7. 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.
  8. In the Select Database dialog box, locate the sample database Northwind.mdb, select it, and then click OK.
  9. 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 HelpIndex for "parameter queries."

For more information about make-table queries, search the Microsoft AccessHelp 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 toMicrosoft Excel, search the Microsoft Access Help Index for "Excel,exporting Microsoft Access data to Microsoft Excel," and then display thetopic "Export a table or query to a spreadsheet."
too few parameters expected 1 2 3 4 5 6 7 8 9 0
Properties

Article ID: 171850 - Last Review: 12/04/2015 17:29:11 - Revision: 4.1

Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition, Microsoft Query 2000

  • kbnosurvey kbarchive kberrmsg kbinterop kbprb KB171850
Feedback