SQL Aggregate functions, at least appear, to only work with database fields, not report controls.
--- In [email protected], "whowhatwhenaware" <stirthing...@...> wrote: > > > > 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 [email protected], "erikd_legendvalve" > <erikd_legendvalve@> wrote: > > > > 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? > > >
