Knowledge Base

ACC97: PivotTable Wizard Fails with Parameter Query in Microsoft Access

Article ID: 166355

Article Last Modified on 1/19/2007

Microsoft Access 97 Standard Edition

This article was previously published under Q166355

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

SYMPTOMS

When the data source that is used in the PivotTable Wizard is a query that contains a parameter, 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 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.

STATUS

This behavior is by design of Microsoft Excel.

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb in Microsoft Access 97.
Create a new form.
In the New Form box, click Pivot Table Wizard, and 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.
In the PivotTable Wizard, click Next on the first screen.
Select all the fields in the Available Fields box, and then click Next. Note that you receive the error message mentioned in the "Symptoms" section.

REFERENCES

For more information about the PivotTable Wizard, search the Help Index for "PivotTables, creating," or ask the Microsoft Access 97 Office Assistant.

Keywords: kberrmsg kbprb KB166355