=sum(nz([Carded Capacity]) is not working

[Amileaux]

In a text box control on a report footer I have the following:

=sum(nz([Carded Capacity]) and it does not work. (I get message that JET could not execute the SQL statement because it contains a field that has an invalid data type).

If I change it to:
=nz([Carded Capacity]) does work. But then I only get the value of the last record.


The Carded Capacity field has nulls. I do not have any problems when I force 0 in where there are nulls - (then I also don't need the nz function). However, I am curious as to why I must force 0's into my table, when I thought the nz function converted nulls to zero's? And why would adding "sum" make it not work? Thank you. Marie

 

[pdx_man]

Try:

=sum(nz([Carded Capacity],0))

 

[Amileaux]

Thank you - but it still is not working. I get the same error message. Marie

 

[Amileaux]

Do you think it matters that the "source" is a union query?

 

[Amileaux]

Okay - I just answered my own question. It is the union query - if I create a table from the union query - no problem with Sum([field name]). I don't need the nz even. However, as soon as I change the source to my union query - the same calculation does not work. Marie

 

[Amileaux]

Thank you!!! Sum(CDbl(YourField)) changed the error message to "Invalid use of Null" - however, Sum(CDBL(Nz(YourField),0))) Worked!! Thanks for your patience. Marie