formula in text box problem

[kwooten]

If I do Sum(January Gain) I get values. There are no values for January loss, but when I do Sum(January Gain)- Sum(January Loss) I get blank data. What am I missing?

[Robeen]

Try this:
Sum(nz([January Gain]),0) - Sum(nz([January Loss]),0)
The nz function puts a Zero in if any of your values are Null. Access can then calculate properly.
Let us know if this helps.

[kwooten]

Syntax error (comma) in query expression...

Should I go into my query and make the null values for "January loss" 0?

[kwooten]

Should I go into my query and make the null values for "January loss" 0?

This didnt work either.

[Robeen]

My bad - sorry.

Try this:
Sum(nz([January Gain],0)) - Sum(nz([January Loss],0))

Let me know if that works

[kwooten]

Should I go into my query and make the null values for "January loss" 0?

This didnt work either.

Actually, this did work..

[kwooten]

My bad - sorry.

Try this:
Sum(nz([January Gain],0)) - Sum(nz([January Loss],0))

Let me know if that works

This worked... thanks! saved me from editing all that data.

[Robeen]

To answer your question:

Should I go into my query and make the null values for "January loss" 0?

I usually use the Nz()function in my queries so I'm sure that the reports are getting no Nulls.

I would put this:

 
Balance: nz([January Gain],0) - nz([January Loss],0)

in a new field in my query in Query Design View.


Then I would just pull the Balance field from the query into my report.

The only calculations I leave to the reports are usually just the Group Totals and the Grand Totals.