ACC2000: PivotTable Wizard Fails with Parameter Query in Access

ID: Q198930

The information in this article applies to:

SYMPTOMS

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

When you use a data source that contains a parameter in the Pivot Table Wizard, you may receive the following error message when the wizard tries to create the table:

An error occurred while trying to use the Microsoft Excel Pivot Table Wizard. Microsoft Excel returned the following error:

'Problems obtaining data.'


CAUSE

You cannot use a parameter query in the PivotTable Wizard. The data used in the PivotTable Wizard must come from a table or a query that does not need any additional input.


RESOLUTION

If you want to use a parameter query as the source for the data in the PivotTable Wizard, you must first run the query and make a table of the data; then, you can use that table in the PivotTable Wizard.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb in Microsoft Access.


  2. Create a new form.


  3. In the New Form box, click Pivot Table Wizard, select the Employee Sales by Country query in the Choose the table or query where the object's data comes from box, and then click OK.


  4. In the PivotTable Wizard, click Next on the first screen.


  5. Select all the fields in the Available Fields box, and then click Next.


  6. When Microsoft Excel opens, click Finish in the PivotTable Wizard dialog box.


Note that you receive the error message mentioned in the "Symptoms" section.


REFERENCES

For more information about creating a PivotTable, click Microsoft Access Help on the Help menu, type "pivot table" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: PRB link datasource 


Keywords          : kberrmsg kbdta IntpOlea QryParm 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999