[AccessD] Nz function in Access 2002

Hi Charlotte

I cannot comment on the value of the article as I don't have A2002.

For the Nz() function, I've never seen any problem with it in A9x or
A2000 except for the case I describe in the other (re)posting.

Perhaps one of our fellow listers could collect what Nz() DOES return
in A2002??

/gustav


> From the same article:

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