Results 1 to 11 of 11
  1. #1
    Joe9 is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2016
    Posts
    4

    Access 2007 undefined function 'nz' in expression

    I have a Access 2007 app that's been used for years. 2007 mde front end & mdb back end, both on same PC. I use a Sagekey installer.




    Over the last year, users started getting an error "undefined function 'nz' in expression". When you launch my app and click a couple buttons all is fine. Then click one button and "undefined function 'nz' in expression" message, rather than opening the screen it should. You have to quit the app and relaunch it. Usually the 2nd time you launch it all is fine. Quit and relaunch and you get the error again. So it's random. It happens on Windows 7 & 10 and on many PCs.


    My App is written in VBA with dao recordsets. I used the Nz function all over the place. Never had a problem with it.


    Surely the code is sound as it's worked flawlessly for many years.


    Any ideas what causes this? Or how I can fix it?

    Joe9

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    sounds like you may have a dodgy reference - check that your libraries are selected OK, particularly any that are versioned (thinking Microsoft Access XX.X Object Library).

    I presume all users are using their own copy of the front end and are running 2007? same bit? since you have a (presumably 2007 generated) mde front end

  3. #3
    Joe9 is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2016
    Posts
    4
    Ajax,

    Thank you for your thoughts on this. I have not developed this app since 2007. To check the references I'd have to open the mdb file (that was used to create the mde file). I don't dare do that with Access 2016. I no longer have a PC with Access 2007. I'd have to create a test PC with Access 2007. Ouch. Why would the references break this year after working for all those other years just fine? I have the same installer package and installing a fresh version of my App does not fix the problem.

    I have clients using Office 2013 and my Access 2007 app works find on those PCs so it seems to be Office 2016 that breaks my app.

    Any other thoughts to fix my app? Or am I just out of business?

    Joe

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    you didn't respond to the second part of my post - does each user have their own copy of the front end? If not, this can lead to corruption - can take a few years and little glitches are perhaps ignored until it stops working. So if your users are sharing the same front end, this is probably the reason why.

    Also, you didn't mention 2016. Support for access 97 mdb's has ceased, so if your mdb was written in 97 and not properly upgraded to 2003 then that might also be a problem.

    see this link https://support.microsoft.com/en-us/kb/2755119

  5. #5
    Joe9 is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2016
    Posts
    4
    Ajax... again thank you.

    Yes... each user has their own front end. Some cases are single users so they have both FE and BE on their PC. Other companies have the BE on a server and each client has a front end on their PC. Both cases are getting this error. Keep in mind, I can reinstall a new FE and BE and get the error. So it's not like with continuous use there is corruption. New files give error.

    It happens on both windows 7 Pro and windows 10 pro (both 64bit). It happens with or without other software loaded. One client actually reformatted a hard drive reloaded windows, disabled the firewall and anti-virus, loaded no additional software and a few days later it happened

    I started development of my App in 1997 and worked on it day and night ) thru 2007 so both the FE & BE mdb's were continuously worked on. I always upgraded to the current version of Office and Access. If there was an upgrade procedure I'm sure I would have done it with each access upgrade. When the upgrade was to an accdb file however I did not do that upgrade. I stayed with the mdb format. I always changed the FE to an mde file before deploying. In the early years I deployed using MS's developer extensions. Later I switched to the SageKey deployment wizard.

    My app uses the old user level security on my BE, which has been discontinued but still works on mdb files

    Any thoughts to what's causing this? Or how to fix it?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    regret no - they were the obvious ones - the only other thing I can thing of is different bit for ms Office (32/54 bit) bit for Windows shouldn't matter. You say you always upgrade to current version, but quoting using .mdb/.mde so not a full upgrade and using mdw which I think is not useable in 2016 - check this link out https://confluence.medschl.cam.ac.uk...ageId=12095276

  7. #7
    Joe9 is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2016
    Posts
    4
    Problem FIXED!!! When you uninstall my app from the control panel (again using the SageKey wonderful technology), it does not uninstall the Access 2007 runtime. It was the runtime that was having issues. The fix is simply to use the control panel to uninstall the runtime. Then reinstall my app (which installed a new Access 2007 runtime.)

    FIXED!!! Thank you Ajax for making me think!.

    FIXED!!!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    Glad you got it sorted!

  9. #9
    Mike51 is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    7

    NZ function not working for Access 2010 on Windows 10 OS

    We are updating our company users' OS to W10 and when testing Access 2010 queries, we get the error message "Undefined function NZ in expression". Any quick remedy for this without rewriting queries with changing expressions to "iif([field] Is Null,0,[field]), which works but will take time to locate all and correct.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    welcome to the forum but you have hijacked an old thread. Please start a new thread

    best I can say nz function is perfectly valid in 2010. It suggests your references are not correct. Please post your question by starting a new thread

  11. #11
    Mike51 is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    7
    Sorry. New to me. will do

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-23-2016, 11:14 AM
  2. Undefined function "format" in expression
    By mithrush in forum Queries
    Replies: 9
    Last Post: 01-22-2016, 09:21 AM
  3. Undefined Function
    By DS928 in forum Access
    Replies: 8
    Last Post: 07-09-2013, 03:53 PM
  4. Undefined function 'chopit' in expression
    By Modify_inc in forum Access
    Replies: 8
    Last Post: 08-20-2012, 04:42 PM
  5. Undefined Function "Left" in Expression
    By krueck in forum Access
    Replies: 4
    Last Post: 09-30-2011, 10:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums