Query Linked to Excel No Longer Available From Data Link

[RobertIngles]

Hi guys;

Strange one:
I have an excel spreadsheet linked back to a query in my Access DB for displaying a progress bar chart. Worked perfectly except that I wanted null values in the query to appear as zero in the Excel sheet. I inserted the nz() expression into the Access query for the fields that were returning null, ran the query and they now show zero as the value. Perfect!

Problem is that when I went back to the Excel spreadsheet, the query is no longer showing as available for use. To test I went back to the Access query and restored it to the original version which returned the null values, checked the Excel sheet and sure enough the query is again available. I thought that maybe it was because the query had an expression in the feilds I wanted to show as zero so I created a query based on the original query and it still doesn't show up as available in the Excel sheet.

Any ideas?

Thanks everyone!!!

 

[RobertIngles]

Update (sadly not a solution)

My research shows that crosstab queries cannot be used in Excel - y'all probably knew that but if you didn't it appears that the SQL Transform function does not travel by ODBC.

Still trying to figure out why a select query based on the crosstab shouldn't work so still looking for some help - would really like to know why Excel doesn't like query results that include zero's instead of null values.

Some suggestions came up regarding using a make table query and using that data in the spreadsheet I may go that way and write a macro to update all the tables each day but it would be so much more convenient to base my results on a query that updates the data in Excel every time the data is refreshed from Excel.

Still hoping for some opinions from the members!