Summing Certain Records

[James K]

I have table that contains a field called [pieces]. This field is formated
as a numerical field and is used to account for the total number of pieces of
pipe that come in and out of our yard with each load. Pipe out is identified
by a negative value.

I have a report that sums the total pieces in the yard by customer or bill
of ladding. However, I want to create an expression that sums only those
values that are positive. This would represent the total pieces that have
come into the yard.

My formula reads =sum([pieces]) where [pieces]>0 but this does not appear to
be working. Is there a way to sum only the positive or negative values in a
field?

 

[fredg]

I have table that contains a field called [pieces]. This field is formated
as a numerical field and is used to account for the total number of pieces of
pipe that come in and out of our yard with each load. Pipe out is identified
by a negative value.

I have a report that sums the total pieces in the yard by customer or bill
of ladding. However, I want to create an expression that sums only those
values that are positive. This would represent the total pieces that have
come into the yard.

My formula reads =sum([pieces]) where [pieces]>0 but this does not appear to
be working. Is there a way to sum only the positive or negative values in a
field?

To sum Positive values:
=Sum(IIf([Pieces]>0,[Pieces],0))

To sum negative values:

=Sum(IIf([Pieces]<0,[Pieces],0))

which will return a negative value.
To convert the value into a positive value, use:

=ABS(Sum(IIf([Pieces]<0,[Pieces],0)))