SQL Aggregate functions, at least appear, to only work with database fields, not report controls.
--- In AccessVBACentral@
>
>
>
> This isn't the cause of your problem, but you should know that you're not declaring your variables correctly. Your statement:
>
> Dim agg_Yr1_Grand, agg_Yr2_Grand, agg_Yr3_Grand As Double
>
> is actually declaring the first two as Variant data type, not Double. The correct syntax is:
>
> Dim agg_Yr1_Grand as Double, agg_Yr2_Grand as Double, agg_Yr3_Grand As Double, or
>
> Dim agg_Yr1_Grand as Double
> Dim agg_Yr2_Grand as Double
> Dim agg_Yr3_Grand as Double 'I prefer this since it's harder to forget to declare the data type.
>
> Anyway, try summing the amounts in the report itself using text boxes that contain a formula instead of doing it in code. Put the text boxes in your report footer with a formula in the Control Source field, like =sum(me.ct105) or something like that, whatever field you're trying to add up.
>
>
> --- In AccessVBACentral@
> >
> > 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(
> > 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(
> > 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?
> >
>
Nenhum comentário:
Postar um comentário