Trouble Using Access Query for Excel Pivot Table

[Jimbo Razza]

Hi There

I'm trying to use a query I created in Access to drive a pivot table in excel. Problem is I've created a function in Access to do a calculation and when I try and import the data for the pivot it says excel does not recognise the function.

Is there a workaround for this?

 

[SydneyGeek]

If it's easy to replicate the function / calculation in Excel, consider creating a version of the query without the calc, importing that, and doing the calc in Excel.
I know you can run Excel functions from Access by setting a reference to Excel and I assume you should be able to do the reverse, but I don't know if that will let you run anything but the built-in functions.

Alternatively, have you tried using the query as an external data source for the pivot table? This gets around the import, and might do the job for you.
The option to use an external data source is on the first screen of the pivot table wizard.

Denis