Sum in Complex Report

[sabascal]

Hi,
Quick question about Sum in complexe reports.

Here is my report Structure:

>GroupesLevel_1>GroupesLevel_2>GroupesLevel_3

Let say that in each of those groupes I have a textbox [Price] with a price.
In each GroupesLevel (2 & 3) footers I want the sum of the [Price] for this groupe.

I used:
in controle source: = Sum([Price]).

It worked for GroupesLevel_2, not for GroupesLevel_3


Any idea why?

Cheers,
Seb

 

[Sdaddy]

I would double check your sorting and grouping box, to make sure you are grouping on each value. I would also double check your syntax in group 3 to make sure you have your equals sign before sum, and that it is spelled correctly.

Hope you find it.




Sean

 

[BSman]

I've basically given up on using the running sum property and the sum function you've described for adding values for subtotal (grouping) levels in reports.

What I normally do is create controls at each grouping level to hold the values and then calculate the values in VBA code. For example:

in detail: Item
Group 3: Subtotal3
Group 2: Subtotal2
Group 1: Subtotal1

You will have to experiment about whether to put the VBA code in the OnFormat property or the OnPrint property at each level, but here's basically what I would do. Assume for the moment that everything will be code behind the OnFormat property.

Detail:
me.Subtotal3 = nz(me.Subtotal3) + nz(me.Item)

Group 3 header:
me.Subtotal3 = 0
Group 2 footer:
me.Subtotal2 = nz(me.Subtotal2) + nz(me.Subtotal3)

Group 2 header:
me.Subtotal2 = 0
Group 2 footer:
me.Subtotal1 = nz(me.Subtotal1) + nz(me.Subtotal2)

Group 1 header:
me.Subtotal1 = 0 '(this insures that the report starts with the grand total equal to 0)

You may have to attached the VBA code for some or all of the Group levels to the OnPrint. I've found that if you encounter doubling of values, that you've probably got code attached to the OnFormat property of levels that are processed twice by Access (because they are OnFormat, rather than OnPrint).