NZ function not working in Access2010 when updating OS from W7 to W10

[Mike51]

Hi, We are updating our company users from Windows 7 to 10 and in testing we discovered that queries that work fine on W7 yeild an error message: "Undefined function in Nz expression" I just set up a new Access2010

query

on W10 and used an NZ function as in a common query we use but got still this message when I ran it. Saved the query and opened Access on a W7 box and ran the same query with no squawks. Thanks for guidance

[pbaldy]

That error is often a reference problem. Check them in the VBA editor, Tools/References, see if any are marked Missing.

[ashraf]

Hi Mike51,
System type can be a reason for such error; moving from 32-bit to 64-bit for windows or for office required to check the references as pbaldy said.
This is a helpful link to Check
Also for similar case Check Josh Black reply.

[Mike51]

Thanks. No references marked missing and the noted references in the link ashraf recommended were all checked. Still selected ok , debug, compile and continued to get the undefined function in nz expression error.

The other link, though similar, is not the same issue, I believe. One suggestion we received was to change Nz([table[.[field]) expression to iif([table].[field] Is Null,0,[table].[field]). This works but we would prefer to avoid changing a number of queries. I am using a 64-bit machine for W7 and W10. Using same Access2010 db, we do not get the undefined function error message in w7 but do in w10.

[pbaldy]

Just so we're clear, you checked references on a Win 10 machine that was failing? One option you could try is creating your own Nz() function using the IIf() or an If/Then block. That should mean you don't have to change the Nz() function in the various places that use it.

[Mike51]

Yes, Checked references in the Win 10 machine that is failing. (Also checked on an alternate machine just to be sure it was not a specific machine issue.) Sorry, I am not sure how to create my own Nz() function using the substitute IIf() or an If/Then block. I can check with a local developer and will let you know if this works. i am still puzzled why the change in the OS if affecting it and whether upgrading to Access2016 may resolve it.

[CJ_London]

I presume the tables you are querying are access tables and not sql server or other where nz is not a recognised function

[pbaldy]

Yes, Checked references in the Win 10 machine that is failing. (Also checked on an alternate machine just to be sure it was not a specific machine issue.) Sorry, I am not sure how to create my own Nz() function using the substitute IIf() or an If/Then block. I can check with a local developer and will let you know if this works. i am still puzzled why the change in the OS if affecting it and whether upgrading to Access2016 may resolve it.

It is odd; usually a reference problem causes this. Creating your own wouldn't be hard; post back if you need help with it.

[Mike51]

Thanks Paul. I am not code writer at all....but I welcome your offer for assistance and will likely pass on your suggestion to an associate if will likely be able to follow it. I would prefer a "global" fix rather that needing to hunt for every query and rewrite functions one at a time.

[pbaldy]

Quick and dirty (put in a standard module):

Public Function Nz(varValue As Variant, varReplace As Variant) As Variant  
  If IsNull(varValue) Then
    Nz = varReplace
  Else
    Nz = varValue
  End If
End Function

since somebody will chime in with a one-liner I'll give you that too:

Nz= IIf(IsNull(varValue), varReplace, varValue)

[Mike51]

Thanks Paul, will give this a try and advise results.

[pbaldy]

No problem, post back if you still have trouble. I tested that and it worked as expected.