When you use MS Query to return data to an excel sheet you can store parameters in a cell or you can have the query prompt users for input.

When you want the query results to be used in a pivot table you cannot have parameters.

I had some code that used a Microsoft Access Query to get around this. The query output goes into a pivot cache. I'm attempt to convert that code to use MS Query rather than Access as I've experienced better response time.

The previous code to get the data from Acces appears below. The converted code appears further below.

The pivot table generated by the new code does not include all of the fields produced by the query. I'm getting vendor name (ASNAME), and invoice number (AIINV) but not the invoice amount (AIORIG).

It appears that the pivot table is created before the complete record set has been obtained. If anyone has any suggestions I would greatly appreciate it.

This is the code for the Access Query:

Please Login or Register  to view this content.
This is the converted code to use MS Query:


Please Login or Register  to view this content.
In another module I copy the record set into the sheet (code below) so I know that they query is okay.

Please Login or Register  to view this content.
Sincerely

Jim P