I received the following error message when trying to create a new database query in Excel 2003:
Can’t access table C:Documents and Settings…My DocumentsTester.mdb.qryTest.
I found a few knowledge base articles, but nothing relevant to my situation. I almost never use the wizard to create external data queries, but I thought I’d give it a shot. Using the wizard, I get this error:
Undefined function ‘Nz’ in expression.
Well that’s a little more helpful. In fact, I do use the Nz function in the query I’m trying to bring over. I changed from:
SELECT tblTest.MyID, tblTest.MyName, tblTest.MyNumber, Nz([MyNumber],100) AS Expr1 FROM tblTest;
to
SELECT tblTest.MyID, tblTest.MyName, tblTest.MyNumber, Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1 FROM tblTest;
and it all works fine. Oh, except for this persistent little problem.
The moral is that MSQuery doesn’t like the Nz function.
Coalesce returns the first nonnull expression among its arguments.
How about this:
COALESCE([MyNumber,100) AS Expr1
in place of
Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1
Coalesce returns the first nonnull expression among its arguments.
How about this:
COALESCE([MyNumber],100) AS Expr1
in place of
Iif(IsNull([MyNumber]),100,[MyNumber]) AS Expr1
I left the ] off the end of MyNumber the first time.
Isn’t NZ a native Access function? I’m not surprised that MSQuery doesn’t recognise it.
It’s for this reason of compatibility issues between different flavours of SQL that I find it a good policy to use stored procs (SQL Server and other ‘proper’ RDBMSs) or parm queries (Access) to do all of the SQL work in my applications wherever possible.
Yep, Nz() is a method of the Access.Application object, so isn’t available on JET db connections outside of Access. I just happened to write a little about this last November, if anyone’s interested:
http://ewbi.blogs.com/develops/2007/11/why-no-nz-via-j.html
Nevermind – apparently COALESCE doesn’t work from MS Query when going against an Access database. It does work from MS Query with a SQL Server database.
In general I’ve stopped using MS Query (in favour of OLEDB) because of the number of things it doesn’t like – union queries being one of them.
Go via the ‘Import Data’ option in DataImport External Data and choose the JET OLEDB provider. Then you can use any Access syntax you like (at least as far as I’ve found). There’s no GUI but I don’t expect that’s a problem.
The only thing it won’t do is parameter queries so you still have to use ODBC for that.
Rob: That’s freaking awesome! I may never use MSQ again. Writing my own parameter code may be worth it just to use this interface.
If you want a GUI for OLEDB queries, try my Query Editor tool, available on my website.
That was about my reaction when i discovered it! I’d spent 4 years using weird systems to get round the lack of support for union queries.
Quite why MS hide it away so much I have no idea.
If you get some example parameter code, I’d love to see it.
I’ve just had the same sort of problem. In my case the root cause was that I created the MS Access Query logged into MS Access as myself.
When I created the query using the “admin” user, I could link the query into Excel. No idea why, but at least it works now.
S.
Hi Rob,
Maybe I do sth wrong, but I do not even see my UNION-ALL-AND-NZ-FUNCTION query to connect to when using JET OLE DB connection…
Any idea what I might do wrong?
Jeez, sad that O.P. was never answered above. In case anyone is still using Office 03 (and you are a massively faster worker if you are, even if you can do 9 conditional format elements in 2007), and has a dual installation with a later version, and go back and forth with versions, this is almost certainly the solution to the original problem:
Close all Access Windows.
Open Access 2003.
Open the .MDB.
Opening Access 2007 plain and purely caused nz() to resume working in Access 2003 for me giving the useless and incorrect “undefined function” error message. In my case, and likely yours, the problem was that I had opened Access 2007, which fires off a reconfiguration; but when all Access instances are closed and you then open Access 2003, 2003’s configuration fires off, causing 2003 to [fully] work again.
Dick, I suspect that based on the post date that you were suffering in 2007 with a dual installation, decided you had to actually get something done so you went back to 2003, and got that badly misleading message.
2007, 2010 and 2013 are so, so cluelessly and deproductively designed compared to 2003, even with their minor improvements…I continue with Excel (and Access) 2003 and will hold on until torn from my cold dead hands.