Displaying a totals amount for a group in the group header

  • Thread starter Thread starter John Baker
  • Start date Start date
J

John Baker

I have used VBA to calculate a totals amount for a field (excluding some
values) across a group. The value is copied correctly to a textbox in the
group footer. The total is initialised to zero in the groupheader on format
event and calculated for each pass of the detail section. The total is
displayed correctly in the group footer.

What I would like to do is display the total correctly in the group header
and not the footer. Any ideas on how to do this?

Any help greatly appreciated.

John Baker
(e-mail address removed)
 
Why did you think you needed VBA. This is rarely required. For instance to
total [HoursWorked] where WorkType <> "Free", you would use:
=Abs(Sum([WorkType]<>"Free" * [HoursWorked]))
You might be able to use a similar expression.
 
John said:
I have used VBA to calculate a totals amount for a field (excluding some
values) across a group. The value is copied correctly to a textbox in the
group footer. The total is initialised to zero in the groupheader on format
event and calculated for each pass of the detail section. The total is
displayed correctly in the group footer.
Click to expand...

You're just lucky, for now. Using code in an event
procedure to calculate a total is NOT reliable! Report
records/sections are not processed in a sequential manner,
which means that your code may count the same values more
than once.

Whenever possible, you should use an aggregate function
(Count, Sum, etc) to calculate the total for you.

In some cases you may have to resort to using a text box
with its RunningSum property set to Over Group (for a group
total) and another set to Over All (for a grand total).

There are even occassions when at least part of the
calculation needs to be done in the report's record source
query.

What I would like to do is display the total correctly in the group header
and not the footer. Any ideas on how to do this?
Click to expand...

I can think of only three ways to get a total in a group
header. By far the easiest is to use an aggregate function.
Another is to calculate the total in the report's record
source query. The third method is pretty tricky and I'd
rather not go into it if either of the other ways can be
made to do the job.
 
Thanks very much for you advice Duane.
What would you suggest I use if I want to count a field value but I didn't
want to count duplicate values


John Baker
(e-mail address removed)
Duane Hookom said:
Why did you think you needed VBA. This is rarely required. For instance to
total [HoursWorked] where WorkType <> "Free", you would use:
=Abs(Sum([WorkType]<>"Free" * [HoursWorked]))
You might be able to use a similar expression.

--
Duane Hookom
MS Access MVP


John Baker said:
I have used VBA to calculate a totals amount for a field (excluding some
values) across a group. The value is copied correctly to a textbox in the
group footer. The total is initialised to zero in the groupheader on format
event and calculated for each pass of the detail section. The total is
displayed correctly in the group footer.

What I would like to do is display the total correctly in the group header
and not the footer. Any ideas on how to do this?

Any help greatly appreciated.

John Baker
(e-mail address removed)
Click to expand...
Click to expand...
 
Sort by the field you want to count and add a group header. Add a text box
in the group header:
Name: txtCountGroup
Control Source: =1
Running Sum: Over All
Visible: No

Then, in the report footer, add a text box:
Control SOurce: =txtCountGroup

If this doesn't work then you may need to create a totals query and include
it in the report's record source.

--
Duane Hookom
MS Access MVP


John Baker said:
Thanks very much for you advice Duane.
What would you suggest I use if I want to count a field value but I didn't
want to count duplicate values


John Baker
(e-mail address removed)
Duane Hookom said:
Why did you think you needed VBA. This is rarely required. For instance to
total [HoursWorked] where WorkType <> "Free", you would use:
=Abs(Sum([WorkType]<>"Free" * [HoursWorked]))
You might be able to use a similar expression.

--
Duane Hookom
MS Access MVP


John Baker said:
I have used VBA to calculate a totals amount for a field (excluding some
values) across a group. The value is copied correctly to a textbox in the
group footer. The total is initialised to zero in the groupheader on format
event and calculated for each pass of the detail section. The total is
displayed correctly in the group footer.

What I would like to do is display the total correctly in the group header
and not the footer. Any ideas on how to do this?

Any help greatly appreciated.

John Baker
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top