summing prob

[loneranger27]

Hi,

I have a report with with a unit header and then a detail section which may have several items in the list and a unit footer. For each line in the detail section there is a payable amount. In the unit footer I have an unbound text box to calculte the total payment and if the payment is a negative it returns a 0 The code in this box is =IIf(Sum([payable])<0,0,Sum([PAYABLE])). this works fine. I cannot seem to get these values to total in the report footer. I want to sum the sums of payable to be placed in the footer and I am having a real problem any help is appreciated.

Thanks

 

[SuicidED]

Hi put a text box in your footer with the control source =Sum(IIf([payable])<0,0,[PAYABLE]))

 

[loneranger27]

That doesnt work, when I use it I get "cannot have aggregate function in expression "

Any other ideas ?

Thanks

 

[SuicidED]

Hi
I think your unit footer control source should be
=Sum(IIf([payable]<0,0,[PAYABLE]))
and exactly the same in the report footer. I've tried it an Access97 and it's fine.

 

[loneranger27]

Hi,

That code will work but it doesnt sum the negative values for the unit.

In the unit detail there will me more than one record to show up some will be positive and some negative. I want to first sum all the positives and negatives to arrive at a total for the unit. This total for the unit however cannot be negative if it is then It needs to show a 0 in it. I then need to sum all the unit sums to arrive at a report total for all the sums of units.


unit header
unit detail
[payable] (+)
[payable] (-)
[payable] (-)
unit footer
works fine [iif(sum([payable])<0,0,sum([payable]))]
report footer
[?] I want to sum all unit footers here.

any help is appreciated.
thanks

 

[SuicidED]

Hi well if I understand you right you want to include the negatives in the report total. Then your control source for this in the report footer should be =Sum([payable]). The other answer is the sum of unit totals and they do not include negatives.

Eddie

 

[loneranger27]

I got it to work i used a running sum and got the result i was looking for.

Thanks gor the help.