NZ function and linking to Excel

[ppataki]

Dear All,

I have a few Access databases where I have certain queries the results of which are displayed in Excel, so I created Pivot tables in Excel with reference to an external data source: my Access database

My problem is that it works fine except when there is an NZ function used in the query, in that case I cannot even see the query in the selection list of the external data source in Excel
I also tried to create another select query based on the query with the NZ function but Excel still cant see the new query either
Solution could be to make a make-table query but that I find rather cumbersome....

Could you please advise how to make those nz queries visible/usable for pivots as well?

Many thanks in advance

 

[raskew]

You might want to go back and delete this post, since it's a duplicate.

Bob

 

[ppataki]

I think now this is the only one, I cant see the other

 

[PearlGI]

Did you ever get a solution to this?
I have the same problem and can't locate a solution.

 

[ppataki]

Hello!
As far as I remember the only solution is not to use the NZ function but to use eg. IIF or SWITCH functions instead
Or if it is about converting text to numbers use CINT or CDBL functions

Cheers

 

[Alansidman]

A workaround, but slightly cumbersome. Convert your Select query that has the NZ function in to a Make Table query. Run the query. Now use Excel to retrieve the data from the new table

Alan