Access query not showing as sources for pivot table

[Chuck W]

Hi,
I have Access 2003 and Excel 2007. I have successfully linked several
access queries to excel pivot tables. However, there is a select query in
access which does not show up in the list of queries to choose from. I
cannot figure out why. The query links a query to a table and runs
successfully in access. It doesn't have an parameters or anything like that.
Are there reasons that anyone knows of as to why a select query would not
appear as a data source for a pivot table?
Thanks,

Chuck

 

[Michael Toomey]

You might be using a function in Access that Excel and MS Query does not recognize. I have used the nz function to convert nulls to zero in MS Access queries and these queries do not show as available because nz is not recognized by excel or ms query. I would place a bet on that. Its a year late but maybe someone else will read and benefit from this question.

 

[Michael Toomey]

I replaced the MS Access query function nz(freight) with

IIf(IsNull(freight),0,(freight) and the query became

available for use as a pivot table source.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Silverlight 4 Martin Fractals with WriteableBitmapEx
http://www.eggheadcafe.com/tutorial...4-martin-fractals-with-writeablebitmapex.aspx