Undefined function 'Nz' in expression (1 Viewer)

bob fitz

AWF VIP
Local time
Today, 22:52
Joined
May 23, 2011
Messages
4,717
Can anybody explain why I get the error message in this thread title when I try to open query1 of the attached db. The SQL for this query is:
Code:
SELECT Table1.ID, Nz([Num],0) AS n, Table1.txt
FROM Table1;
DB attached

Thanks in advance
 

Attachments

  • NZtest.accdb
    412 KB · Views: 109

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Did you check if you have any missing reference?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
Hi Bob. The problem I found with your db is you have a circular reference because the name of your Textbox where you have the Nz() function is the same as the name of the field you're trying to calculate. Try renaming the Textbox into txtNum and keep the expression as Nz([Num],0). Hope that helps...
 

bob fitz

AWF VIP
Local time
Today, 22:52
Joined
May 23, 2011
Messages
4,717
Hi Bob. The problem I found with your db is you have a circular reference because the name of your Textbox where you have the Nz() function is the same as the name of the field you're trying to calculate. Try renaming the Textbox into txtNum and keep the expression as Nz([Num],0). Hope that helps...

I don't think that I have a problem with Nz() in the form.
I get the error when I try to run the query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
I don't think that I have a problem with Nz() in the form.
I get the error when I try to run the query.
Well, that's interesting. I didn't have any problems running the query, but got a #Size! error on the form.





 

Attachments

  • nz.png
    nz.png
    5.7 KB · Views: 625
  • frm.png
    frm.png
    5.6 KB · Views: 608

Gasman

Enthusiastic Amateur
Local time
Today, 22:52
Joined
Sep 21, 2011
Messages
14,048
Bob,
That query on it's own works for me no problem?

I get #Error in the form for Num.:confused:
 
Last edited:

plog

Banishment Pending
Local time
Today, 17:52
Joined
May 11, 2011
Messages
11,612
theDbguy got it right in post #3

Query1 runs fine for me

Form1 shows error in Num input when I open it initially

Form1 shows 0 in Num field when when I change the Name of the input box to "Numx" and leave your formula as Nz([Num],0)
 

isladogs

MVP / VIP
Local time
Today, 22:52
Joined
Jan 14, 2017
Messages
18,186
Query works for me as well but there is definitely something wrong with the form

You have the expression = Nz([Num];0) with a semicolon
I renamed the control txtNum to get rid of the circular reference and tried to alter the expression to =Nz([Num],0) but I now get an invalid syntax error
Creating a new form didn't help either!
Reverting to = Nz([Num];0) gave a value 0

Very odd!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:52
Joined
Oct 29, 2018
Messages
21,358
Query works for me as well but there is definitely something wrong with the form

You have the expression = Nz([Num];0) with a semicolon
I renamed the control txtNum to get rid of the circular reference and tried to alter the expression to =Nz([Num],0) but I now get an invalid syntax error
Creating a new form didn't help either!

Very odd!
Hi Colin. Just curious, what does it say to use as a default list separator in your Reqional Settings?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:52
Joined
Sep 21, 2011
Messages
14,048
Query works for me as well but there is definitely something wrong with the form

You have the expression = Nz([Num];0) with a semicolon
I renamed the control txtNum to get rid of the circular reference and tried to alter the expression to =Nz([Num],0) but I now get an invalid syntax error
Creating a new form didn't help either!
Reverting to = Nz([Num];0) gave a value 0

Very odd!

I have comma in both of mine and Bob shows up as in the UK? :confused:
 

isladogs

MVP / VIP
Local time
Today, 22:52
Joined
Jan 14, 2017
Messages
18,186
Hi Colin. Just curious, what does it say to use as a default list separator in your Reqional Settings?

Doh! I temporarily changed the language settings to Italian to answer another thread and must have forgotten to change them back
 

Micron

AWF VIP
Local time
Today, 18:52
Joined
Oct 20, 2018
Messages
3,476
FWIW, you could use an IIF expression instead of changing the control name. However, I've oft said that I do not design with field and control names being the same, so Nz should not be a problem where that is the case.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 28, 2001
Messages
27,001
I was able to open the query.

Why did you post a Firefox message when the problem should have been an Access message box?
 

Micron

AWF VIP
Local time
Today, 18:52
Joined
Oct 20, 2018
Messages
3,476
References are fine but still can't use Nz() in any query, in any db :banghead:
That seems like a different problem than what I saw in your db example. The form displayed an error for me - because of the circular reference. Also, the query runs no problem. That db only has the 4 basic references. If you're certain about the references, maybe you've uncovered a new update bug?
 

bob fitz

AWF VIP
Local time
Today, 22:52
Joined
May 23, 2011
Messages
4,717
Why did you post a Firefox message when the problem should have been an Access message box?
Sorry for my ignorance but I don't understand this question. The message I posted is the message that I get when I try to open any query that uses the Nz() function.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 28, 2001
Messages
27,001
Whoops, my bad. It was my own message because I hadn't defined a Firefox association. But i was able to open the query without error.
 

Dreamweaver

Well-known member
Local time
Today, 22:52
Joined
Nov 28, 2005
Messages
2,466
I just built a complex query where I have a couple of nz() functions think I have the latest update the one with the query corrupt error havnt had a problem with the 2.5 mill dataset
 

Users who are viewing this thread

Top Bottom