Undefined function 'Nz' in expression

[bob fitz]

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:

SELECT Table1.ID, Nz([Num],0) AS n, Table1.txt
FROM Table1;

DB attached

Thanks in advance

 

[theDBguy]

Hi. Did you check if you have any missing reference?

 

[theDBguy]

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]

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 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.

 

[Gasman]

Bob,
That query on it's own works for me no problem?

I get #Error in the form for Num.

 

[plog]

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]

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]

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]

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?

 

[isladogs]

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

 

[Deleted member 147267]

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.

 

[bob fitz]

Hi. Did you check if you have any missing reference?

References are fine but still can't use Nz() in any query, in any db :banghead:

 

[The_Doc_Man]

I was able to open the query.

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

 

[Deleted member 147267]

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]

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]

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]

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

 

[fertooos]

Hi. Did you check if you have any missing reference?