Nz function in queries RRS feed

  • 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

    Saturday, June 22, 2019 5:32 PM

All replies

  • 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)

    Saturday, June 22, 2019 5:57 PM
  • 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

    Saturday, June 22, 2019 6:04 PM
  • 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

    Sunday, June 23, 2019 11:41 AM
  • Wow! At least you've got it working again!

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

    Sunday, June 23, 2019 12:29 PM