Nz has started throwing up an error message

[Alc]

I can't see this specific problem described already, here or via Google, but I apologize in advance if I was just searching using the wrong terms.

Many of the Access databases I look after include some variation of

If Nz(txtApproval, "") <> "" Then

Where Nz is used to replace a Null value in the txtApproval field with an empty string.
These have been working without issue for years.

As of today, any line that uses the Nz function and has a cell which IS blank, produces an error message

"Run-time error '-2147352567 (80020009)': You entered an expression that has no value"

I should stress that the first db in which this happened hasn't changed in at least three months and has been used regularly since then.

It may be a coincidence but this week we got a software updated that included some changes for Office 365.

I've tried:

• Commenting out the problematic line of code, in case the issue was control-specific, but it just runs to the next one where Nz is used and displays the same error message
• Testing in multiple databases. All produce the same error
• Checking for compilation errors. None found
• Putting a 'dummy' value into the field in question. No error occurred
• Checking for missing references. None were labeled 'MISSING' or not displayed at all. Microsoft Access 16.0 Object Library is still present
• Using a message box to display the value in that field just before the Nz line. In this case, I get "Run-time error '2427': You entered an expression that has no value", which I expected to get

Does Nz not work any more?
Do I need to declare something extra or add a different reference?
Is there another way of handling blank fields on a form?

It's bad enough if I change something and it stops working. When a feature was fine one day and not the next, without me touching the db, it puzzles me.

 

[Gasman]

Rollback that update.

 

[Alc]

Rollback that update.

Thanks. I've requested it but it's company wide so won't happen.
I was, effectively, told that I have to work with it.

 

[DHookom]

In addition to Gasman's suggestion, you could temporarily try:

If txtApproval & "" = "" Then

 

[Alc]

In addition to Gasman's suggestion, you could temporarily try:

If txtApproval & "" = "" Then

Hadn't seen that way of doing it before. Thanks.
Unfortunately, same error message.

 

[Alc]

Found a way around part of the problem.
This code

    If Not IsNull(txtApproval) Then
        txtOrigApprover = IIf(IsNull(txtApproval), "A", "B")
    End If
    MsgBox txtOrigApprover
    MsgBox txtApproval

sets txtOrigApprover to "B", suggesting that txtApproval is NOT null.
However, the second message box then produces the "Run-time error '2427': You entered an expression that has no value" message

 

[Gasman]

Thanks. I've requested it but it's company wide so won't happen.
I was, effectively, told that I have to work with it.

Not sure how you are going to do that TBH, and very shortsighted on your IT/Employers part.
So glad I am retired now.

Perhaps @isladogs who I believe has 365 might be able to reproduce the issue, or anyone else.
Can you supply your version and build number etc.

 

[Alc]

Not sure how you are going to do that TBH, and very shortsighted on your IT/Employers part.
So glad I am retired now.

One of the downsides of working for a big corporation.
Access is looked down on as a bit of a childish application.
IT want us to switch to something they will build, but the department manager won't allow it as it would slow down enhancements (and take years to build).
Because IT don't control it, they don't care about it.
Because they don't care about it, they'll often make changes that cause me headaches.

 

[Gasman]

One of the downsides of working for a big corporation.
Access is looked down on as a bit of a childish application.
IT want us to switch to something they will build, but the department manager won't allow it as it would slow down enhancements (and take years to build).
Because IT don't control it, they don't care about it.
Because they don't care about it, they'll often make changes that cause me headaches.

Yes, I worked for a bank just like that.
However I believe it to be a bug, and if the Access team are told about it, they will eventually fix it.
You would need to supply full build number so others can compare.

 

[Mark_]

One of the downsides of working for a big corporation.
Access is looked down on as a bit of a childish application.
IT want us to switch to something they will build, but the department manager won't allow it as it would slow down enhancements (and take years to build).
Because IT don't control it, they don't care about it.
Because they don't care about it, they'll often make changes that cause me headaches.

It is worse than that. Until you are doing something IT needs, you are a USER. IT will NEVER accept that they are the problem when a USER tells them.

 

[Pat Hartman]

Are you 100% sure you are getting the same message if you switch to the suggested expression? Is the second error happening on the same line?

 

[isladogs]

@Alc
Unable to reproduce your issue in 365 version 2502 build 184514.20000 (Beta Channel)
I use Nz regularly in my apps and have never had any issues with it. Nor have I seen any other reports of this issie.

As previously requested, it would be helpful to know your Access version, build and release channel.

 

[MajP]

Usually if vba functions stop working it is caused by a broken reference. Not necessarily the vba reference. Please post a screenshot of your references.

 

[Alc]

Thanks for the feedback, gentlemen.

The version, etc. is as follows

The references are as follows

 

[Alc]

Are you 100% sure you are getting the same message if you switch to the suggested expression? Is the second error happening on the same line?

Sorry, missed this response.
Yes and yes.

As far as I can work out, txtApproval is not null but also has no value.
If I try to assign a value just before the error line, I get told I can't assign a value to it.

 

[MajP]

What is the control source to TxtApproval? If for example it is a calculated control you cannot assign a value to it and would in fact get that error. I think the same thing if the source is not editable.

 

[Alc]

What is the control source to TxtApproval? If for example it is a calculated control you cannot assign a value to it and would in fact get that error. I think the same thing if the source is not editable.

Not the cause here, unfortunately. It's a field called 'Approval' in a table.
I just tried recreating the control, in case there was something 'off' with the original, but no change.

I'll find some workaround, it's just going to take time.

 

[The_Doc_Man]

Call up your references again, then scroll through them one at a time to verify that none of them show "Missing" or "Broken" - the two most common "gotcha" events after a Microsoft Office update cycle.

 

[DHookom]

It doesn't sound like the application has been changed but I would expect something like this if a user defined function or module was named Nz. This would also suggest If txtApproval & "" = "" Then would not be a problem.

 

[gemma-the-husky]

You might get that sort of error if you have no records, and a non updatable record set.

In that case, all variables are undefined if you will.

Is that possible?