Null to Zero - result as a number not string

[Guest]

How do you get Nz function to return a zero in a query. If I use Nz(Data,0),
I get a string with the digit '0' (zero) stored in it. How do I get a
numeric value of zero as a result? I am using access query designer not SQL.

Thanks

 

[JohnFol]

What data type is the "Data"?

 

[Allen Browne]

Yes, this is a common problem with JET. It is quite poor at determing the
data type of calculated fields.

Try explicitly typecasting, e.g.:
CDbl(Nz(data, 0)
or
CLng(Nz(data, 0)
or
CCur(Nz(data, 0)

More info in:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Guest]

It is type 'Currency' in the underlying table - is that the cause of my
problems?

 

[Guest]

Thanks Allen. I have prefixed my calcs with CCur and it works like a dream!

Other than your website, where do I find the doco for this?

 

[Allen Browne]

I'm not aware of other doco for this issue.

This practical stuff comes from hard-earned experience - my own, and others
in these newsgroups.