Function NZ not recognised (1 Viewer)

Bumpkin · Jun 5, 2019

I have a new laptop with access 2010 installed using the installation disc from my previous laptop and accdb files also taken from the old laptop.
I am now getting Undefined function NZ errors which I have never had before.
I have checked the references and they are all the same as on the old m/c.
After some checking and unchecking references the problem went away. However, as a test, I copied the accdb file and ran it again - the problem is back and I can't get it to go away.
Any ideas?

Gasman · Jun 5, 2019

Another user had the same problem very recently. Apparently down to two bitness versions of Access on the computer. They preferred 64 bit.?

Bumpkin · Jun 5, 2019

I have only loaded the 32 bit default version. I don't think it is possible that I have two versions bit is there any way I can check?

isladogs · Jun 5, 2019

It shouldn't be possible to have both 32-bit and 64-bit Access on the same PC but that user somehow had that. Its highly unlikely you will have without knowing it.

What is more likely is that you will have a missing VBA reference library.
The first time that library is referenced you will get an error. This time its Nz, next time it could be another function like Date …

Go to the VBE, CLICK Tools...References then see if any are marked as MISSING..
If so, browse and find the location of that reference to fix.

If that isn't the case, make a backup then try compacting, then decompile followed by recompile and finally compact again. Good luck and ask if any of that is unclear.

Bumpkin · Jun 5, 2019

SOLVED????
I tried the repair feature - no change.
I am not sure what compile and decompile are in the 2010 version. I have only worked with accdb files but isladogs post made me fish around the options.
I found save and publish as accde. This appears to be an uneditable version of the accdb file.
Miraculously the accde file works with no error! A total mystery to me as to why the unrecognised function error should disappear.

marlan · Jun 5, 2019

Bumpkin,

What about the references?

isladogs said:
Go to the VBE, CLICK Tools...References then see if any are marked as MISSING..
If so, browse and find the location of that reference to fix.

VBE - Visual Basic Editor.

That would be my guess, try that before going to the decompile - compile option.

Bumpkin · Jun 5, 2019

Hi Marlan,
The references are all fine, ie none missing.
The references selected are also identical in the two accdb versions: one working ok and the other copied from the working version gives the error.

References selected are:
Visual Basic for applications
Microsoft Access 14.0 Object library
OLE Automation
Microsoft Office 14.0 Access database engine

Are these correct ones for office 2010?

Bumpkin · Jun 5, 2019

Not solved!!!
As a test I set up a simple accdb database with NZ in a query. Sure enough the "function not recognised" error was duplicated. I then published as an accde database but this time it did not cure the problem.

isladogs · Jun 5, 2019

References are correct
Compile - in the VBE (visual Basic Editor), go to Debug...Compile.
Fix any errors arising.
Decompile - removes any corrupted code - see http://www.fmsinc.com/microsoftaccess/performance/decompile.asp for instructions & explanation

Its fine to create / distribute ACCDE files but make sure you keep the original ACCDB so you can do further development work

If the above doesn't solve it, suggest you upload a stripped down copy of the ACCDB file for someone to look at

marlan · Jun 5, 2019

Bumpkin said:
Not solved!!!
As a test I set up a simple accdb database with NZ in a query...

Colin, is the Nz function in VBA and Ace the same function? would compiling issues be a good solution for function errors in queries?

isladogs · Jun 5, 2019

The Nz function used in Access VBA is part of the default Access reference library.
ACE is the name of the database engine used by Access (previously JET)

Compiling a database should be done regularly as a matter of course during development work.

Before doing so tick Require Variable Declarations in the VBE Tools ... Options.
This will add Option Explicit as the 2nd line in all new code modules
However you will need to manually add that line at the top of all existing code modules.
Once done, compile the database & it will flag any issues you need to fix e.g. variables not defined.

If after compiling you still have problems, you may need to run an Office repair or reinstall

Hope that helps

Bumpkin · Jun 5, 2019

Here is a stripped down accdb file.
The table contains handicaps, some of which are blank.
The intention is to extract the handicap values substituting zero for the blank values.
On the new laptop I get "Unrecognised function NZ" when I run the query.
This is the reason for my original request for help.

On the old laptop it runs but the results are not exactly as expected and raises more questions.

The test query has the following columns:
* HCapPerTbl - this is the value in the table : Handicap
* HCapDesired - this is the desired value to be extracted : NZ(Handicap)
I expected numerical values but I seem to have text with no conversion on the blank fields
* test1_Handicapx1 - This is to test multiplying [Handicap] by 1 to do the conversion
As expected the result of multiplication including a blank field gave a blank result.
* test2_NZHandicapx1 - testing multipliying NZ([Handicap]) by 1 to do the conversion
This worked but I don't know why the result is different to the first column HCapDesired
test2_NZHandicapx1 - Similar to above but multiplying NZ([Handicap]) by 1 to do the conversion

I hope you guys like a challenge - I am baffled.

theDBguy · Jun 5, 2019

Bumpkin said:
Here is a stripped down accdb file.
The table contains handicaps, some of which are blank.
The intention is to extract the handicap values substituting zero for the blank values.
On the new laptop I get "Unrecognised function NZ" when I run the query.
This is the reason for my original request for help.

On the old laptop it runs but the results are not exactly as expected and raises more questions.

The test query has the following columns:
* HCapPerTbl - this is the value in the table : Handicap
* HCapDesired - this is the desired value to be extracted : NZ(Handicap)
I expected numerical values but I seem to have text with no conversion on the blank fields
* test1_Handicapx1 - This is to test multiplying [Handicap] by 1 to do the conversion
As expected the result of multiplication including a blank field gave a blank result.
* test2_NZHandicapx1 - testing multipliying NZ([Handicap]) by 1 to do the conversion
This worked but I don't know why the result is different to the first column HCapDesired
test2_NZHandicapx1 - Similar to above but multiplying NZ([Handicap]) by 1 to do the conversion

I hope you guys like a challenge - I am baffled.

Hi. Pardon me for jumping in... Some of the explanations are simple while others require us to assume certain things. For example, here's a screenshot of what the Help file says about the Nz() function.

As stated above, if you don't provide a replacement value for nulls, then you get back an empty string (blanks). And of course, doing math with null values results in nulls. As for why multiplying Nz()*1 results in zero (0), this is where we assume that sometimes, Access tries to be helpful. We can assume that Access "understood" you're dealing with numbers by multiplying something by 1, so it must have used a default/replacement value of 0.

Bumpkin · Jun 5, 2019

Thanks DBGuy - I guess I got so frustrated with the "Unrecognised function" that my test query didn't make sense - part two above solved.

theDBguy · Jun 5, 2019

Bumpkin said:
Thanks DBGuy - I guess I got so frustrated with the "Unrecognised function" that my test query didn't make sense - part two above solved.

Hi. You're welcome. To avoid unexpected results, it might be a good habit to get into supplying a replacement value. If you expect to get zeroes, then add it as the default. For example:

Code:

Nz([FieldName],0)

Good luck with your project.

isladogs · Jun 5, 2019

Hi
I've been logged off for a few hours and didn't get to look at your dB.
Can I assume this is now solved? If so, can you mark it as solved using the Thread Tools drop down menu item

Bumpkin · Jun 5, 2019

No I am afraid the original problem is not solved - I still can't get function NZ() to work on the new laptop.
The secondary problem I thought I had was just me but the original problem is still there.

isladogs · Jun 5, 2019

I've now tested it and added the zero default as explained by DBGuy.
The query runs perfectly for me in A2010 with the two Nz fields showing 0 where the first column is null.

Suggest you next run an Office repair on the laptop with issues. See my comments in post #11

Bumpkin · Jun 6, 2019

Thanks for trying everyone,
Everything works fine on my old m/c but I cannot get function NZ to work on the new laptop.
I have tried reinstalling office2010, repairing it, installing updates, repairing the accdb file but nothing sees to work : I keep getting unrecognised function NZ.
Oddly if I go into the VB editor it finds function FZ with the object browser in the Access.Application library.

There must be something wrong with my new laptop but I can't figure out what.
If anyone has any ideas please let me know.

Function NZ not recognised (1 Viewer)

Registered User.

Enthusiastic Amateur

Registered User.

MVP / VIP

Registered User.

Registered User.

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

I’m here to help

Attachments

Registered User.

I’m here to help

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Similar threads

Users who are viewing this thread