Calculating the difference across multiple records in a report

[kjclark]

This is probably an easy one, but I'm stuck! I have a report that shows the volume and sales per quarter. I now want to show the difference between quarters to show if sales are increasing or decreasing from the previous quarter, but I can't figure out how to write the expression since there is only one field (Sales) which shows per quarter in the report.

Any help would be much appreciated!

Thanks!
Karla

 

[Norris68]

In your report code module...

Dim LastSales As Currency
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
lblDifference.Caption = Format$(Sales - LastSales,"Currency&quot
LastSales = Sales
End If
End Sub

You have to mess about with the Retreat event as it is possible for the report to be rolled back a few records if required during formatting.

 

[kjclark]

Thank you Norris68 - this worked great!

But now, as I try this code for the other fields that I need to calculate, I am getting this error:

"The expression OnFormat you entered as the event property setting produced the following error: Ambiguous name detected: Detail_Format"

Do I need to do something different for the next field I need to calculate besides re-naming the variables?

Thanks again for your help!
Karla

 

[Norris68]

Put them all within the same event procedure - there can only be one. e.g.

Dim LastSales As Currency
Dim LastCosts As Currency '*New*
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
lblDifference.Caption = Format$(Sales - LastSales,"Currency&quot
LastSales = Sales
lblCostDifference.Caption = Format$(Costs - LastCosts,"Currency&quot '*New*
LastCosts = Costs '*New*
'etc.
End If
End Sub

 

[kjclark]

Ah ha! Thank you so much!

 

[kjclark]

Worked perfectly! Thanks for the help!