Subtracting two values in the same field

[Guest]

I have created a report based off of a table. In my report I have listed
months, for example in this report I have listed Sept and Oct data. Next
month I will have Oct and Nov. and so on. I am trying to subtract a Sept
value from and Oct value. However, both values I have are in the same field
so I can use [xyz]-[xyz]. Is there a way to subtract these values. I have
attached an example.

TV Viewers Shows Commercials
Oct 1365 62 105
Sept 1258 69 168
Difference 107 7 63

I need to calculate the Difference field in my report.

Thank You

 

[Allen Browne]

One way to do this is to use the events of the report. Store the previous
figures in the Print event of the section, and assign them in the Format
event.

This example assumes your report has text boxes in the Detail section,
named:
-Viewers, Shows, Commercials (bound to fields), and
-txtPriorViewers, txtPriorShows, txtPriorCommercials (unbound) for showing
the previous values.

1. In the General Declarations section of the report's module (at the top,
with the Option statements):
Dim mlngViewers As Long
Dim mlngShows As Long
Dim mlngCommercials As Long

2. In the Format event procedure of the Detail section, add this code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
mlngViewers = Nz(Me.Viewers, 0)
mlngShows = Nz(Me.Shows, 0)
mlngCommercials = Nz(Me.Commercials, 0)
End If
End Sub

3. In the Print event procedure of the Detail section, add this code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.txtPriorViewers = mlngViewers
Me.txtPriorShows = mlngShows
Me.txtPriorCommercials = mlngCommercials
End Sub

One weakness of the above approach is that if you print only some pages of a
report (e.g. starting at page 7), the first record may not have the correct
value. Since it did not print page 6, the value will not have been picked
up, and so won't be shown.

If you don't want to do this in a report, an alternative approach might be
to use subqueries:
http://allenbrowne.com/subquery-01.html#AnotherRecord