Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Null to Zero Function

Discussion in 'Information Technology' started by Nancy, Oct 27, 2005.

  1. Nancy

    Nancy
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh woman's forced marriage annulled in Britain (New Kerala) Breaking News Oct 22, 2008
    Sikh News Sikh woman’s marriage annulled in UK (Hindustan Times) Breaking News Oct 22, 2008
    Concept Of Sunn-Void,nullity, Essays on Sikhism Jan 16, 2008
    Legal Commemorating International Day of Zero Tolerance to Female Genital Mutilation Breaking News Feb 6, 2011
    Visiting Punjabi NRIs give zero, Gujaratis shower $450 billion Hard Talk Jan 21, 2011

  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Try this
    Val(NZ(qryReport_Group![0 <20]),0) AS [0<20]

    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Nancy" wrote:

    > 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?
    >
    >
     
  4. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    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" <Nancy@discussions.microsoft.com> wrote in message
    news:C9F5EC39-AD05-4E7E-9F37-639B34039632@microsoft.com...
    > 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?
    >
    >
     
  5. Nancy

    Nancy
    Expand Collapse
    Guest

    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" wrote:

    > 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" <Nancy@discussions.microsoft.com> wrote in message
    > news:C9F5EC39-AD05-4E7E-9F37-639B34039632@microsoft.com...
    > > 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?
    > >
    > >

    >
    >
    >
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <Nancy@discussions.microsoft.com> wrote in message
    news:9062C0BC-62EC-4487-BB05-4A353FAC1B43@microsoft.com...
    > 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" wrote:
    >
    >> 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" <Nancy@discussions.microsoft.com> wrote in message
    >> news:C9F5EC39-AD05-4E7E-9F37-639B34039632@microsoft.com...
    >> > 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?
    >> >
    >> >

    >>
    >>
    >>
     

Share This Page