Undefined Function Error in Pivot Table Using Access Query

[cityboy3]

I am using an Access query as the data source for an Excel pivot table. My Access query uses a custom function which generates the "undefined function" error when I try to base a pivot table on that query. I tried pasting the function into an Excel module, but that didn't help. I'm guessing that I would need to rewrite the query to return the original data and then use the function in excel to get the results that I want. Unfortunately, I need to do the calculation in Access because these results are part of a sub-query that needs to be processed further before returning results to the pivot table. Any ideas?

thanks

 

[bkpchs237]

cityboy3,

Try turning your query into a make-table query, then using that table as the data source for your Excel pivot table instead. In this manner the custom function would have no bearing on the data source. Of course, you'd need to refresh the data prior to each use, i.e. rerun the make-table query to update the information, but you could setup a macro to delete the table and recreate it again as needed.

Hope this helps.

 

[cityboy3]

Thanks. I wish I could do that -- it would make life simpler. But I need to set this up for users who don't have a clue how to use access. They need to open up their spreadsheet and refresh the pivot table. I guess that if there's no way to get past the undefined function, I'll have to set up VBA code to do what you described.