Question Query can no longer be accessed from Excel. Nz() may be to blame. (1 Viewer)

[wmphoto]

Hi all.

I have a query which I have connected to an excel worksheet. The query displays as a table in excel. I've been using it for a while without any problems. Recently I've decided to reorganise the data that goes into the query, however the query itself hasn't changed, there's a chain of queries all feeding data into each other to arrive at this final query, and it's further up the chain that I did the reorganising.

Now when I try to open the workbook, I get this error (the workbook is set to refresh data whenever it is opened)

The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganised, then try the operation again.

If I try to edit the data connection or create a new connection, the query I've been using also no longer appears in the list of queries and tables I can get data from.

The problem is, this wasn't the sort of reorganisation that I could do incrementally and then see what specifically caused the connection to fail. The query was out of action altogether until I'd finished the reorganising. So it's a bit complicated and I don't know what kind of things could cause a query not to be available to make a connection with Excel.

I am able to see from the list of queries I can connect to where the first query in the chain comes that I can't connect to. The only thing about this particular query worth mentioning is that it uses the Nz() function (it adds together sales figures by month from a list of sales, and in months where there are no sales, there is no data, hence null, when logically I want it to be zero, hence the use of the function).

So my question, before I start fiddling around with queries again to find another way to get my figures without using Nz()... does anyone know if the Nz() function is definitely the reason I can't connect to excel.

 

[RainLover]

The NZ is unlikely to be a problem.

Try to connect via a new query to prove you can or cannot.

Personally I would connect to excel as if it were a table.

 

[Alansidman]

Before you start fiddling, I would urge you to try and do a compact and repair first. See if that solves your issue. If that fails, then perhaps, open a new database and import all your objects into the new database. Then try to establish your connection with the new database and see if your query is there. If either of those work, then you probably have a corruption in your original database.

Alan

 

[wmphoto]

The NZ is unlikely to be a problem.

Try to connect via a new query to prove you can or cannot.

Personally I would connect to excel as if it were a table.

Thanks, what do you mean, connect as if it were a table? As far as I was aware, that was what I was doing.

 

[RainLover]

I was unsure what you were doing.

Are you telling me that you can see all of the Excell Data through a Link in the Tables.

If this is correct then your query is incorrect. You just need to go through and run each one until you find the fault.

 

[wmphoto]

I was unsure what you were doing.

Are you telling me that you can see all of the Excell Data through a Link in the Tables.

If this is correct then your query is incorrect. You just need to go through and run each one until you find the fault.

Ah, no it is a link to access from excel. The data in the query is (or was) shown in excel as a table (table being the excel formatting style, not the access object type).

I'll give the suggestions here so far a try over the weekend.

 

[boblarson]

Are the series of queries all just Select queries?

 

[wmphoto]

No, the queries that come before the 'Nx()’ query are crosstab queries, they are the same queries that existed before but most of my reorganisation comprised using a different field for the crosstab column.

 

[RainLover]

Ah, no it is a link to access from excel. The data in the query is (or was) shown in excel as a table (table being the excel formatting style, not the access object type).

I don't understand this statement.

Do you have a Link as a Table in Access to the Excel Worksheet.?

 

[boblarson]

I don't understand this statement.

Do you have a Link as a Table in Access to the Excel Worksheet.?

As I understand it, it isn't that they are using Excel as a linked table in Access, they have linked to a query in Access from the Get Data functionality in Excel (where you can connect up to an Access database to display Access data in Excel and it be able to refresh either automatically or on command.

 

[wmphoto]

Yeah exactly, nothing has been done in Access to create this link it is purely Excel based, but there is something about the query that Excel doesn't like and won't 'see'.

 

[RainLover]

Thanks Bob.

Armed with that info I will move aside.

 

[wmphoto]

Tried a few things... turns out it was the Nz() function, when I replaced it with IIf(IsNull(Field),0,Field), the thing works.