No subject

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

And ...

Although the Nz Method is correctly documented in Microsoft Script
Editor Help, it does not function as expected

However, the article doesn't explain what it *DOES* do--that unexpected
behavior.  

I've never had any problems with Nz, so I can't agree with your "flaky"
assessment.  It works for me, yet the article says it "does not function
as expected".  Maybe I have different expectations?

Charlotte Foust



-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Friday, March 07, 2003 4:43 AM
To: Charlotte Foust
Subject: Re: [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?

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com