# Suming Positive Numbers Only

1. Competent Performer
Windows XP Access 2003
Join Date
Apr 2009
Location
Houston, Tx
Posts
164

## Suming Positive Numbers Only

Is there a way in Access to create an expression in a report that will sum only the positive numbers in a field?

2. Competent Performer
Windows XP Access 2000
Join Date
Apr 2009
Location
Custer, WA, USA
Posts
125
Could you clarify what you mean?

Given a sequence of
5
-2
3

should the sum be
5+3 = 8
or do you want the sum of the absolute values thusly...
5 + 2 + 3 = 10

3. Competent Performer
Windows XP Access 2003
Join Date
Apr 2009
Location
Houston, Tx
Posts
164
Originally Posted by CraigDolphin
Could you clarify what you mean?

Given a sequence of
5
-2
3

should the sum be
5+3 = 8
or do you want the sum of the absolute values thusly...
5 + 2 + 3 = 10
Hello Craig,

Sorry I was not more clear. I was looking for a way to get the sum of the positive numbers only which would create a result of 8 in your example.

4. Competent Performer
Windows XP Access 2000
Join Date
Apr 2009
Location
Custer, WA, USA
Posts
125
Ok, then the idea would be to create a caluclated field in the query which your report is bound to.

Something like:
NewField: IIF(Nz([OriginalFieldNameHere],0)<=0,0,[OriginalFieldNameHere])

This basically says, if the numbers is less than or equal to zero, then use a value of 0. Otherwise, use the value unchanged. And if the field is null, then pretend it's a zero.

Then simply sum the new field.

5. Competent Performer
Windows XP Access 2003
Join Date
Apr 2009
Location
Houston, Tx
Posts
164
Originally Posted by CraigDolphin
Ok, then the idea would be to create a caluclated field in the query which your report is bound to.

Something like:
NewField: IIF(Nz([OriginalFieldNameHere],0)<=0,0,[OriginalFieldNameHere])

This basically says, if the numbers is less than or equal to zero, then use a value of 0. Otherwise, use the value unchanged. And if the field is null, then pretend it's a zero.

Then simply sum the new field.
Beautiful, That worked perfectly. Thank you very much!

6. Competent Performer
Windows XP Access 2000
Join Date
Apr 2009
Location
Custer, WA, USA
Posts
125