Accumulator Double Counts

[LarryDeLaruelle]

I have set up two accumulators to count records based on the value of one of the report's fields.

The two variables (integers) are declared as module level and are initialized in the Report_Open event to zero.

In the Detail_Format event I have an if then statement testing for a null value; if not null add one to the accumulator; else no action. I also increment a general accumulator to count each record.

In the ReportFooter_Format event I assign the values of the two accumulators to text controls in the report footer section and assign one percentage calculation to a third control. These three text controls and one label control are the only controls in the report footer.

For some reason, the ReportFooter_Format event is triggering twice. On the first pass, the values are correct; on the second pass, the values are doubled.

The record source query has one calculated field using an immediate If and one function call.

Can anyone explain why the ReportFooter_Format event is triggering twice?

Thanks in advance.



Larry De Laruelle

 

[genomon]

All format events fire a minimum of two times (more if there are group level footers & headers), and this includes detail format as well. I usually put counters in a print event(i.e. Detail_Print()), which only fires once.

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[BoxHead]

Larry, if you're not using those values elsewhere in your db, you might be able to just use calculated controls:


Null Values = Sum(IIf(IsNull([expr1001]),1,0))
Not Null Values = Sum(IIf(IsNull([expr1001]),0,1))
Record Count = Sum(IIf(IsNull([expr1001]),1,1))
Percent of Records that are Null =Format(Sum(IIf(IsNull([expr1001]),1,0))/Sum(IIf(IsNull([expr1001]),1,1)),"Percent")

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[dhookom]

Like John, I doubt you need to use any code in your report. In addition to the Null counts, you can accumulate all types of conditions. For instance, if you want to count all the females:

=Sum( Abs([Gender] = "Female") )

or count the number of employees who make over 50000 per year:
=Sum( Abs([AnnualSalary]>50000) )

To count all the females making over 50000
=Sum( Abs([Gender] = "Female" AND [AnnualSalary]>50000) )

To sum all the freight charges for deliveries to Chicago:
=Sum(Abs([ShipCity="Chicago") * [Freight])

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]

 

[RoyVidar]

Agree fully with dhookom, but just a little thingie about the on print event - I've seen that event fire more than once a couple of times, too (see the help file for details). One may perhaps use the FormatCount arguement of the event (both format and print) to determine how many times it has fired.

[tt]if formatcount = 1 then
<increment the counter/sums ...>
end if[/tt]

Roy-Vidar