On printing figures double

J

John H W

When I run a report, I needed two separate counts on the same data field (P
or T). Since I could not figure out how else to do it, I wrote a function to
which you send (and return) the "P" or "F". This function just adds one to
the applicable global variable. In the reports footer, I have another
function to which you send either the P or F and it returns the amount in the
global variable.

This works find and the correct amount is displayed on screen, but when you
print the report, the figure on the report is doubled (other printings it may
be tripled, etc., depending on how many times I print the report after
running the initial report).

I have put breakpoints in the functions that adds to the count and returns
the count, but neither is activated when the report is printing.

Any ideas??

John H W
 
D

Duane Hookom

There is probably an easier method if we knew where you are and what your
requirements are. It is very rare that you need to write code in a report to
"tally" any values.
 
J

John H W

Actually, it is a little more complicated than below. I have to "count" the
"F" and "P" and at the same time get 2 averages of another (currency) field,
based on the "F" and "P". The sum of the other values are divided by the
count to get that average.

I have tried using one and two other queries, but there were just too many
variables (such as the record had to be between a set of dates).

What I need to figure out is WHY when I print what is showing on the screen
(which is correct), the count in the two (four) fields are added to the
previous amount in that field.

Hope this helps.
John H W
 
D

Duane Hookom

The format event of a section of a report may fire twice for each printing
of the section.

I still think there might be an easier method that doesn't involve any other
queries or code.
To count the number of "F" values in FieldA, use:
=Sum(Abs([FieldA]="F"))
to average all the FieldCurrency values where FieldA = "F"
=Sum(Abs([FieldA]="F") * FieldCurrency)/Sum(Abs([FieldA]="F"))
You may need to catch for a divide by zero if there are no "F" values in
FieldA.
 
J

John H W

The below worked. I had forgotten that ABS would return a 1 when true.

Thanks,
John H W

Duane Hookom said:
The format event of a section of a report may fire twice for each printing
of the section.

I still think there might be an easier method that doesn't involve any other
queries or code.
To count the number of "F" values in FieldA, use:
=Sum(Abs([FieldA]="F"))
to average all the FieldCurrency values where FieldA = "F"
=Sum(Abs([FieldA]="F") * FieldCurrency)/Sum(Abs([FieldA]="F"))
You may need to catch for a divide by zero if there are no "F" values in
FieldA.
--
Duane Hookom
MS Access MVP


John H W said:
Actually, it is a little more complicated than below. I have to "count"
the
"F" and "P" and at the same time get 2 averages of another (currency)
field,
based on the "F" and "P". The sum of the other values are divided by the
count to get that average.

I have tried using one and two other queries, but there were just too many
variables (such as the record had to be between a set of dates).

What I need to figure out is WHY when I print what is showing on the
screen
(which is correct), the count in the two (four) fields are added to the
previous amount in that field.

Hope this helps.
John H W
Click to expand...
Click to expand...
 
Top