I have several fields in my detail section that are calculated from adding the
values of a bunch of database fields together. The list of fields that are
added changes from month to month. So, in January, bucket 1 is for January,
but in February bucket 1 is for February, and so on and so forth. We then take
those buckets and add the values for to obtain totals for a given year and year
to date. We show the current year and 2 previous years data in the report, so
I have 6 aggregate variables, 3 for the Years' grand totals, and 3 for the YTD
totals.
Now, I need to take those year total and year to date fields and give a grand
total for them at the bottom of the report, a seemingly simple task that has me
going nuts. Here is the gist of my code.
Dim agg_Yr1_Grand, agg_Yr2_Grand, agg_Yr3_Grand As Double
Dim agg_Yr1_YTD, agg_Yr2_YTD, agg_Yr3_YTD As Double
Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
agg_Yr1_Grand = agg_Yr1_Grand + Nz(Me.Ctl05)
Me.grand1 = agg_Yr1_Grand
agg_Yr1_YTD = agg_Yr1_YTD + Nz(Me.YTD2006)
Me.ytd1 = agg_Yr1_YTD
agg_Yr2_Grand = agg_Yr2_Grand + Nz(Me.Ctl06)
Me.grand2 = agg_Yr2_Grand
agg_Yr2_YTD = agg_Yr2_YTD + Nz(Me.YTD2007)
Me.ytd2 = agg_Yr2_YTD
agg_Yr3_Grand = agg_Yr3_Grand + Nz(Me.Ctl08)
Me.grand3 = agg_Yr3_Grand
agg_Yr3_YTD = agg_Yr3_YTD + Nz(Me.YTD2008)
Me.ytd3 = agg_Yr3_YTD
End Sub
Private Sub Report_Open(Cancel As Integer)
agg_Yr1_Grand = 0
agg_Yr2_Grand = 0
agg_Yr3_Grand = 0
agg_Yr1_YTD = 0
agg_Yr2_YTD = 0
agg_Yr3_YTD = 0
End Sub
Everything works beautifully if I do a Print Preview, but as soon as I hit
print, the totals are all out of whack. Mysteriously the totals add in extra
dollars. If I sit there and click the Landscape button over and over, you can
watch the totals go up and up and up and up without bound.
I've tried this in both Access 2000 and 2007, with the same result. I've tried
Compacting and Repairing, with no effect. Any ideas?
