Calculate product in group footer 1

[tekkyun]

Can anyone tell me easy method of calculating product of a detail field within Report group footer instead of sum?
eg
UNIT RESULT
A 2
A 4
A 6
Product of Result=48

 

[HandsOnAccess]

It is possible to calculate product of a detail field within a report. It can be done as follows

In the report design view Place a new text-box (Text1)
Set the Visible Property of the new text-box to False

In the Report Footer Place another text-box (Text2)
Set the Control Source Property of Text2 text box as
=Text1

In the Report VBA Window Place these Lines of Code

Option Compare Database
Dim Product As Double

Private Sub Report_Open(Cancel As Integer)
    Product = 1
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Product = Me.Result * Product
    Me.Text1 = Product
End Sub

Regards,

 

[HandsOnAccess]

In Continuation of my previous post,
You must also place a code to refresh the value of Product each time the Product is displayed in the Footer.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    Product = 1
End Sub

The is calculate Product afresh for each footer.

Regards,

 

[dhookom]

There is no need for code to accomplish this. Add a text box with a control source of:

=Exp(Sum(Log([Result])))

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]

 

[HandsOnAccess]

I checked your above expression, I works great.
Thanks dhookom, way to learn.
Please accept my Star for you.

Only thing I could not figure it out was how to reset the
=Exp(Sum(Log([Result]))) value at the end of each footer?
Its multiplying all.

Regards,

 

[dhookom]

In my tests, this expression works exactly like any other total in a group or report Header or Footer. It will only aggregate values from the appropriate section. The Report Footer calculation will include all records from the report. A group section calculation will only aggregate values from the group.

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]

 

[HandsOnAccess]

Cool, its working for me too...

tekkyun, Just implement dhookom method, Its simple and easy.

Regards,

 

[tekkyun]

Sorry I haven't replied as I have been out of the office all day.
Thank you all for your help. I will try out your suggestions tomorrow and get back if I still have a problem.

Thanks again