NZ Function Undefined (under certain conditions) (1 Viewer)

ajarrell

Registered User.
Local time
Today, 16:27
Joined
Feb 4, 2014
Messages
52
After moving to Windows 10 and Microsoft Office 2019, I am getting this error message in queries and reports: Undefined function 'Nz' in expression.


The error occurs if I start Access by double-clicking on the database file. If I start Access, then Open the file, the error does not occur. This was not the case before the upgrade.


I apologize if this is a duplicate post. I thought I had posted it yesterday but got no response overnight and can't find the post this morning.


Thank you in advance!
 

sneuberg

AWF VIP
Local time
Today, 13:27
Joined
Oct 17, 2014
Messages
3,506
I suggest checking your references. That's in Visual Basic, Tools, References. They may not be in sync with Office 2019.

I have to admire your courage. I'm still with Office 2013 with Windows 7.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:27
Joined
May 21, 2018
Messages
8,463
I concur that this is a reference issue. I would create a new blank DB (ensuring current references are added) then import all the objects into the new db. I always import the tables first and then do a compact repair. Then import all the other objects.
 

ajarrell

Registered User.
Local time
Today, 16:27
Joined
Feb 4, 2014
Messages
52
Thank you, Steve. This is what I found; not sure what to do with it. :rolleyes:


These are checked:
Visual Basic for Applications
Microsoft Access 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Access database engine Object Library
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:27
Joined
May 21, 2018
Messages
8,463
Since few people are on 2019 already. Can you create a blank database and post the default references. Interesting to see if they are updated.

When your code is looking for a function such as NZ, it goes through each library in alphabetical order looking for it.

Microsoft Access 16.0 Object Library
Microsoft Office 16.0 Access database engine Object Library
OLE Automation
Visual Basic for Applications

Since NZ is in VBA if it hits a bad reference prior to getting to VBA, it never gets to VBA and cannot find the NZ function. So even though you may have the most current VBA library it could fail before finding it.

The most common symptom you see are people saying their string functions stopped working (left, right, mid, instr....) since these are common in queries.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:27
Joined
Feb 28, 2001
Messages
27,001
The acid test is if you go to a coding page where some VBA is visible. In the icon bar there is a multicolored icon for Object Browser. Bring that up.

In the complex box, select "All Libraries" then use the Search function to look for NZ to see if it is defined. If so, where. That will tell you which library is involved. (And if nothing comes up, then you have a problem indeed.)

Normally, when you get one of those errors you have the DEBUG option, which would bring you to the section of your code that failed (unless it was in the .ControlSource for a given control's value).

However, another possibility comes to mind. If launching the file directly gives you an error but launching Access first does not, the problem MIGHT be a faulty association (pointing to an older version of Access?) I'm not quite sure where you find Associations under Win10, so you'd have to look that up. But if you find that .ACCDB associations point to the wrong version of Access, that would do it.

If so, you might try to just re-install Access. When that starts, you SHOULD get an option to REPAIR the product. Take that option. It should re-establish correct associations. If that isn't it, then I'm not sure where else to go with it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:27
Joined
May 21, 2018
Messages
8,463
In the complex box, select "All Libraries" then use the Search function to look for NZ to see if it is defined. If so, where. That will tell you which library is involved. (And if nothing comes up, then you have a problem indeed.)
Good catch. NZ is an Access Application function and not a VBA function. My bad. Because of that I am less certain this is a reference issue. I would still rebuild from a fresh DB.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 16:27
Joined
Oct 17, 2012
Messages
3,276
Out of curiosity, what, if anything, do you have running at startup?
 

ajarrell

Registered User.
Local time
Today, 16:27
Joined
Feb 4, 2014
Messages
52
Starting Access, then Opening db file (works properly):
1. Visual Basic for Applications – C:\Program Files\WindowsApps\Microsoft.Office.Desktop_16051.11126.20266.0_x86_8wekyb3d8bbwe\VFS\ProgramFilesCommonX86\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
2. Microsoft Access 16.0 Object Library – C:\Program Files\WindowsApps\Microsoft.Office.Desktop
3. OLE Automation – C:\Windows\SysWOW64\stdole2.tlb
4. Microsoft Office 16.0 Access database engine Object Library - C:\Program Files\WindowsApps\Microsoft.Office.Desktop_16051.11126.20266.0_x86_8wekyb3d8bbwe\VFS\ProgramFilesCommonX86\Microsoft Shared\Office16\ACEDAO.DLL


Double-clicking on db file (gives error on Nz function):
1. Visual Basic for Applications – C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
2. Microsoft Access 16.0 Object Library – C:\Program Files\Microsoft Office\Root\Office16\MSACC.OLB
3. OLE Automation – C:\Windows\System32\stdole2.tlb
4. Microsoft Office 16.0 Access database engine Object Library - C:\Program Files\Common Files\ Microsoft Shared\Office16\ACEDAO.DLL
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious... What do you get if you do the following steps?

1. Open database using shift bypass
2. Go to VBA IDE window
3. From the main menu toolbar, select Debug > Compile Database (or Compile NameOfYourProject)
4. Compact and Repair
5. Open database as normal
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 16:27
Joined
Oct 17, 2012
Messages
3,276
I should have been clearer. What, if anything, do you have the application run (via the AutoExec macro) when you start the application normally?

Although the fact that you get different reference library locations if you load directly rather than if you load Access, then open the app seems a bit odd.
 

sneuberg

AWF VIP
Local time
Today, 13:27
Joined
Oct 17, 2014
Messages
3,506
Although the fact that you get different reference library locations if you load directly rather than if you load Access, then open the app seems a bit odd.

Maybe not so odd. On my Office 2013 system if I double click on an mdb file it uses an access 2010 runtime rather than my Access 2013. I think which version it uses on a double click depends on how the file associations are set up.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:27
Joined
Feb 28, 2001
Messages
27,001
Comparing what you saw for the libraries:

----------------------
1. Visual Basic for Applications – C:\Program Files\WindowsApps\Microsoft.Office.Desktop_16051.1 1126.20266.0_x86_8wekyb3d8bbwe\VFS\ProgramFilesCom monX86\Microsoft Shared\VBA\VBA7.1\VBE7.DLL

1. Visual Basic for Applications – C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL

Suspicious but might not be SO bad since they point to what I would think would be the same files. I'm not sure whether this is more than just worrisome.

------------------
2. Microsoft Access 16.0 Object Library – C:\Program Files\WindowsApps\Microsoft.Office.Desktop

2. Microsoft Access 16.0 Object Library – C:\Program Files\Microsoft Office\Root\Office16\MSACC.OLB

This is a VERY suspicious reference that looks blatantly wrong. I think the link here HAS to be considered "broken."

------------------------------
3. OLE Automation – C:\Windows\SysWOW64\stdole2.tlb

3. OLE Automation – C:\Windows\System32\stdole2.tlb

This is ALSO incredibly suspicious. IN THEORY a .TLB file isn't susceptible to differences between 32-bit and 64-bit versions - but that path difference makes me think something is terribly wrong here. Further, the .TLB itself shouldn't make a difference - but some of the items in the .TLB might be "bitness-differentiated" and that WOULD cause havoc.

-----------------
4. Microsoft Office 16.0 Access database engine Object Library - C:\Program Files\WindowsApps\Microsoft.Office.Desktop_16051.1 1126.20266.0_x86_8wekyb3d8bbwe\VFS\ProgramFilesCom monX86\Microsoft Shared\Office16\ACEDAO.DLL

4. Microsoft Office 16.0 Access database engine Object Library - C:\Program Files\Common Files\ Microsoft Shared\Office16\ACEDAO.DLL

Again, this APPEARS to be two paths to the same (or equivalent) files so this raises less of an alarm than the #2 and #3 references. It is again worrisome because it says the two ways of launching are NOT running the same exact thing.

Some specific tasks & questions for you.

A. Have you ever had 64-bit Access installed on that system?

B. Launch the app both ways. For EACH launch, don't launch the opening form. Instead, bring up the developer's viewpoint with the navigation panel to the left and the menu bar or ribbon at the top.

Go to the File menu and click on the Help topic. On the screen that comes up, look to the right-hand side of what is shown to find the "About Access" header (not the first thing in that column). Then look at which version of Access you are running including the "bitness" of that version. You are looking at a "build" version and a "bitness" declaration that will be either 32-bit or 64-bit.

What I am looking for with B is that you are pointing to two different versions of Access. Since the references are stored in the registry and you are showing TWO DIFFERENT LISTS, that means your registry is showing two different sub-hives for the two launches. This is EXTREMELY troubling to me and speaks of a faulty installation or incomplete prior deinstallation of Access.

Clearly, when you are dealing with the cause of "Launch Access, then launch the app" you are using what Office thinks is the most current installation. But that convoluted references pathway that includes "desktop" references makes me wonder if you are being plagued by Windows trying to get you to download Office365 when you have a perfectly good copy of Office 2019 available. Office tried to do this to me once. I had to go in and find the Office365 "hook" and disable it.

It is imperative that you perform the experiment described in "B" to compare the named versions. My question "A" related to the possibility that you had a mixed "bitness" version because basically, without some effort, an app developed on 32-bit Access does not run so well on 64-bit Access (and vice versa) without some serious - and I DO mean serious - modifications. So we have to assess exactly what you are seeing here in order to give best advice.

Next question: Did you install Office on that system or did someone else do it? If our advice were to be "remove Access/Office COMPLETELY and then re-install it" ... could you? Because this would be a more deep de-install than normal.
 

Minty

AWF VIP
Local time
Today, 20:27
Joined
Jul 26, 2013
Messages
10,355
I think Sneuberg has hit the nail on the head.

It's opening the db with a different app. Possibly run time or an older version of Access, and if you aren't careful, it can do weird things having more than one version installed..
 

ajarrell

Registered User.
Local time
Today, 16:27
Joined
Feb 4, 2014
Messages
52
You guys have led me in the right direction. I find that opening Access first brings up a 32-bit version (which works), while double-clicking the db file gives me the 64-bit version (which gives the error).
 

ajarrell

Registered User.
Local time
Today, 16:27
Joined
Feb 4, 2014
Messages
52
Solved! When I changed my default app to be the 32-bit version, the error went away. I'm not sure why I should be using the 32-bit, but it works.


Thank you all!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:27
Joined
Feb 28, 2001
Messages
27,001
I'm not sure why I should be using the 32-bit, but it works.

You should be using the 32-bit app if that is the app under which the front-end was developed. I'm not sure that a "pure" BE file even CARES about the bitness of Access (if it has no local modules, for example, it wouldn't) but the FE cares a LOT.
 

isladogs

MVP / VIP
Local time
Today, 20:27
Joined
Jan 14, 2017
Messages
18,186
Although you can have 2 versions of Access installed (as I do) it can cause issues as previously mentioned.

However, I didn't think it was possible to have a mixture of 32-bit and 64-bit versions on the same PC unless you use a VM. If you try and install both, you will get error messages and the installation of the second one will fail.
I STRONGLY recommend you uninstall both versions completely then reinstall the 32-bit version in order to fix registry entries etc
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:27
Joined
Feb 28, 2001
Messages
27,001
Having no personal experience with this split bitness version, I defer to our friend Colin's opinion on the right way to proceed.
 

Users who are viewing this thread

Top Bottom