how to total positive and negative #s separately? 2

[GaborH]

On an access report, I am trying to total a column that has negative and positive numbers in such a way that I have separate totals for negatives and for positives. I can not seem to do it without adding a couple of extra fields to my query. Is there a neat way that I can do this on a report?

Any hints are highly appreciated!

Thank you!

 

[BSman]

Let's say that in the detail section of your report you are printing the value of interest (me.txtValue). And from you question I think you are looking for two controls in a grouping or summary section, let's call them me.txtValuePositve and me.txtValueNegative.

Place the following code in the OnPrint or OnFormat property of the detail section:

if me.txtValue >=0 then
' positive value calculation
me.txtValuePositive = nz(me.txtValuePositive) + nz(me.txtValue)

else
' calculation for negative value
me.txtValueNegative = nz(me.txtValueNegative) + nz(me.txtValue)

end if

The nz() function is used so that in case any control is null it will be handled as the value 0. Otherwise those calculations can fail.

Don't forget in the group header section to put the following code in OnPrint or OnFormat:

me.txtValuePositive = 0
me.txtValueNegative = 0

This should work properly if you put these sections of code in the OnPrint property of the appropriate section; however, sometimes it's necessary to use the OnFormat property instead (but never both). If you use the OnFormat property look out for double calculations that can occur sometimes because Access runs through the report twice during preparation.

Bob

 

[dhookom]

Don't use any code. All you need is text boxes in the report footer with control sources of:
=Sum(Abs([YourField]>0) * [YourField])
=Sum(Abs([YourField]<0) * [YourField])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]