Null to Zero Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have an Access Query with the following statement using "ntz" which I am
told is "null to zero".

Val(ntz(qryReport_Group![0 <20])) AS [0<20]

However, I cannot find any documentation on the "ntz" function. I do see
the Nz function. My question is how does this statement work? Is the "ntz"
in the statement an actual function or is it somehow defined somewhere in my
Access query separately.

My real issue is I'm trying to import the results of this query into Excel
and I'm getting a message saying "[Microsoft][ODBC Microsoft Access Driver]
Undefined function 'ntz' in expression". I've tried replacing "ntz" with the
Nz function in the Access Query. The query runs correctly but I'm still
getting the ODBC error message when importing data into Excel.

Any ideas?
 
The correct function is Nz() not Ntz(). By default, the function will return
0 or "" (as appropriate for the data type of the field) if the value being
tested is Null or it will return the value if the value isn't Null. You can
also specify the value to be returned.

In your function, replace Ntz with Nz. To specify a value you would change
what you have to

Val(Nz(qryReport_Group![0 <20], 0)) AS [0<20]

This will insure that zero is returned if the field is Null. If you specify,
you don't have to rely on the field data type being interpreted correctly.

As another example, if you wanted to use today's date if the value is null
you could use

Nz([Datefield], Date())
 
Thanks to both replies. After posting my question, I realized that "ntz" was
defined in a module in my access d/b which explains how it worked.
Apparenlty, this was created before the Nz function existed.

The second part of my problem involved importing the query results into
Excel. However, after changing ntz to Nz in my query and running it
successfully, I tried to import the data into Excel but ran into the same
ODBC error referencing "...Undefined function 'Nz' in expression". If I read
this correctly, the Nz function is not supported in Excel.

Thanks anyway!


Wayne Morgan said:
The correct function is Nz() not Ntz(). By default, the function will return
0 or "" (as appropriate for the data type of the field) if the value being
tested is Null or it will return the value if the value isn't Null. You can
also specify the value to be returned.

In your function, replace Ntz with Nz. To specify a value you would change
what you have to

Val(Nz(qryReport_Group![0 <20], 0)) AS [0<20]

This will insure that zero is returned if the field is Null. If you specify,
you don't have to rely on the field data type being interpreted correctly.

As another example, if you wanted to use today's date if the value is null
you could use

Nz([Datefield], Date())

--
Wayne Morgan
MS Access MVP


Nancy said:
Hello,

I have an Access Query with the following statement using "ntz" which I am
told is "null to zero".

Val(ntz(qryReport_Group![0 <20])) AS [0<20]

However, I cannot find any documentation on the "ntz" function. I do see
the Nz function. My question is how does this statement work? Is the
"ntz"
in the statement an actual function or is it somehow defined somewhere in
my
Access query separately.

My real issue is I'm trying to import the results of this query into Excel
and I'm getting a message saying "[Microsoft][ODBC Microsoft Access
Driver]
Undefined function 'ntz' in expression". I've tried replacing "ntz" with
the
Nz function in the Access Query. The query runs correctly but I'm still
getting the ODBC error message when importing data into Excel.

Any ideas?
Click to expand...
Click to expand...
 
You're correct, the NZ function is available only within Access. An
alternative that should work for you via ODBC is the IIF function, e.g.
something like ...

IIf([SomeFieldName] IS NULL, 0, [SomeFieldName])

--
Brendan Reynolds

Nancy said:
Thanks to both replies. After posting my question, I realized that "ntz"
was
defined in a module in my access d/b which explains how it worked.
Apparenlty, this was created before the Nz function existed.

The second part of my problem involved importing the query results into
Excel. However, after changing ntz to Nz in my query and running it
successfully, I tried to import the data into Excel but ran into the same
ODBC error referencing "...Undefined function 'Nz' in expression". If I
read
this correctly, the Nz function is not supported in Excel.

Thanks anyway!


Wayne Morgan said:
The correct function is Nz() not Ntz(). By default, the function will
return
0 or "" (as appropriate for the data type of the field) if the value
being
tested is Null or it will return the value if the value isn't Null. You
can
also specify the value to be returned.

In your function, replace Ntz with Nz. To specify a value you would
change
what you have to

Val(Nz(qryReport_Group![0 <20], 0)) AS [0<20]

This will insure that zero is returned if the field is Null. If you
specify,
you don't have to rely on the field data type being interpreted
correctly.

As another example, if you wanted to use today's date if the value is
null
you could use

Nz([Datefield], Date())

--
Wayne Morgan
MS Access MVP


Nancy said:
Hello,

I have an Access Query with the following statement using "ntz" which I
am
told is "null to zero".

Val(ntz(qryReport_Group![0 <20])) AS [0<20]

However, I cannot find any documentation on the "ntz" function. I do
see
the Nz function. My question is how does this statement work? Is the
"ntz"
in the statement an actual function or is it somehow defined somewhere
in
my
Access query separately.

My real issue is I'm trying to import the results of this query into
Excel
and I'm getting a message saying "[Microsoft][ODBC Microsoft Access
Driver]
Undefined function 'ntz' in expression". I've tried replacing "ntz"
with
the
Nz function in the Access Query. The query runs correctly but I'm
still
getting the ODBC error message when importing data into Excel.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top