Sum Function Problems

[Luke]

I have created a query that includes "EntityName", "1st Investment", and "2nd Investment". I created an expression using the Builder and called it "Total Invested". My Expression is TotalInvested: ([1stInvestment]+[2ndInvestment]). However when I run the query my Expression only shows a value when there are both a 1st and 2nd investment value. I want the expression to show the total investment even when there's only a 1st investment! For example, if my 1st investment is $20 and my 2nd Investment is $40, my expression will show the total invested to be $60. That's what I want. But when my 1st investment is $20 and there isn't a 2nd investment value ($0), my expression doesn't show anything when I run it in the query. Do you know what function or expression I could use to solve this problem?
Thanks.



-Luke

[alansidman]

You need to incorporate the NZ function into your expression. The NZ function allows Access to process a null record. Here is a link on how to employ the NZ function and apply it to your expression.

http://www.techonthenet.com/access/f...dvanced/nz.php

You will want to make your expression:

TotalInvested: NZ(1stInvestment, 0)+ NZ(2ndInvestment,0)

Alan

[pbaldy]

Wrap each field in the Nz() function:

Nz(FieldName, 0)

[Luke]

That worked perfectly thanks a ton