Nz function in queries

Question

I've encountered some unusual behaviour on a couple of occasions recently when creating queries in new .accdb files.  Each time I've called the Nz function in a query the function has been unrecognised, and an error raised.  No reference is missing or broken, and I can call the function in VBA with no problem.  Any form or report in which the RecordSource property is an SQL statement which includes the Nz function exhibit the same behaviour.

Existing files which include queries in which the Nz function exhibit no error when such a query is opened, and, in those files I can create a new query which calls the function with no problem.  However, if I then delete all the existing objects in the file apart from the new query, the error occurs.

It's easy to work around the problem with an IIf function call of course, but I'd rather avoid this if possible.  Any suggestions will be gratefully received.

---

Ken Sheridan, Stafford, England

Answer 1

That's weird. I cannot reproduce the problem (I have Access 2019 version 1905, in Click-to-Run Office 2019 Professional Plus).

Have you tried repairing your Office installation?

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thanks for the prompt reply, Hans.  A repair, or even a complete reinstallation of Office, is obviously on the cards, but I'd like to see, firstly if anyone else has experienced the problem, and if they have any ideas about the cause and /or solution.

---

Ken Sheridan, Stafford, England

Answer 3

I had no luck with repairing the Office 365 installation, Hans, so had to resort to a complete reinstallation.  Things are now working as expected.

---

Ken Sheridan, Stafford, England

Answer 4

Wow! At least you've got it working again!

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)