Suming Positive Numbers Only

[jbarrum]

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

[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

[jbarrum]

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.

[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.

[jbarrum]

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!

[CraigDolphin]

Glad I could help