Sub Totals at bottom of page 1

[Denz]

ARGH! HELP! I really can’t work this one out, please could someone tell me how to sort this out?

I have created a report that list all the records within a month that is selected when the report is run, the report converts a series of check boxes into a number (3) then a font is used to display to tick. I need to somehow count the number of ticks (or 3’s or true check boxes) on each page, (but not count the records, just the records which have ticks), and then reset the count on the next page, I have many checkboxes on each record, so this needs to be done for various fields.

I also need to have a grand total on the bottom of the report, I assume that I just need to so a sum() on the footer of the report.


Many thanks

Dave Denning

 

[JoeMiller]

Sum the field in question and then apply an absolute value to it. Checked checkboxes have a value of -1 so if you add them and pull absolute you'll have the answer.

=Abs(Sum([MyCheckBoxField]))

HTH Joe Miller
joe.miller@flotech.net

 

[Denz]

That allows me to get a total of check boxes in the report, but i also need to have a total of how many check boxes are on each page also. So there might be 16 on one page and 19 on the next, i need to report on that too.
Is there any way to do that???

Many thanks (Again)

Dave Denning

 

[JoeMiller]

A little VBA will do it. The way it works is you declare public variable, on the page header print event set the variable equal to 0. Then in the print event of each record you add the absolute value of the checkbox field to the variable. Here's the code from the sample I made. My checkbox field was called SelectRec, change accordingly for your scenario.

[tt]
Option Compare Database
Option Explicit
Public PageTotalCount As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
PageTotalCount = Abs(SelectRec) + PageTotalCount
End Sub

Private Sub PageHeader_Print(Cancel As Integer, FormatCount As Integer)
PageTotalCount = 0
End Sub
[/tt]

Now all you have to do is put a control in the page footer that has =[PageTotalCount] as the ControlSource.

HTH Joe Miller
joe.miller@flotech.net

 

[Denz]

Ta mate! Works a treat!