[AccessD] Nz function in Access 2002

Hi Charlotte

> I ran across an MSKB article today
> http://support.microsoft.com/default.aspx?scid=kb;en-us;295619 that says
> the Nz function works in 2002 but it may not work as expected!  Say
> what??   It works, but it doesn't?

How can you read that from the article? It reads:

<quote>

The Nz method of the data source control is supposed to replace Null
values with a zero (0) or some other specified value. However, the
method does not replace Null values as expected.   

</quote>

So it doesn't work in A2002.

However, Nz() has always been flaky - to quote myself from 12. May
2002: 

<quote>

Did you know that - when used to return an expression in a query -
Nz() always returns a string even if it is supposed not to do so?

Like this:
    Expr1: Nz([fldNumeric])
or:
    Expr1: Nz([fldNumeric], 0)

If you need a numeric value, you'll have to wrap it in Int():

    NumExpr1: Int(Nz([fldNumeric]))

Even if fldNumeric is a long, this will return a long when
fldNumeric is not Null but an integer for Null.
If you wish a long in any case, the zero for Null values for some
reason must be present:

    NumExpr1: Int(Nz([fldNumeric], 0))

Alternatively, Nz() can be replaced with the good old IIf()
construction:

    NumExpr1: IIf(IsNull([fldNumeric]), 0,[fldNumeric])

This is tested for Access 95, 97 and 2000. Don't know about 2002.

</quote>

/gustav


> Has anyone run into this?  Nz isn't always the most appropriate
> function, but I've never seen it fail, at least not that I knew about.
> We use this a lot, and I'm concerned about migrating our apps from 97 to
> 2002 and having a lot of code fall over.  I wondered if it could be the
> result of not passing in the optional argument, but the article seemed
> rather vague to me.  Does anyone else have first-hand knowledge of the
> problem?