DAvg in Dynamic Crosstab report footer (1 Viewer)

[WillEllis]

I'm still fairly new to VBA and this is the frist time I have tried to do a dynamic crosstab report(got it from a microsoft link in another thread). What I am looking to do is create an 'Actuals/Forecast' report, with a 12 month Avg and Totals at the end of each row and a group avg and total field in the report footer as well. I was able to get the row avg by calculating that in the crosstab query itself, but getting the column totals to avg in the report footer has me stumped. Here is my code and a screenshot of my report. I keep getting a data type error on the DAvg...

P.S. forgive my coding if it looks messy...never formally trained in this(self taught Access since March...lol)

Option Explicit
Const conTotalColumns = 7

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(4 To conTotalColumns) As Long
Dim lngRgColumnAvg(4 To conTotalColumns) As Double
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 4 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer
For intX = 1 To 3
Me("Head" & Format(intX)) = rstReport(intX - 1).Name
Next intX
For intX = 4 To intColumnCount
' Format Column Heading as a date and add identifier depending on actual data 'Act' or forecasted data 'Fcst'(by way of current date)
If Month(CDate(rstReport(intX - 1).Name)) < Month(Date) Then
Me("Head" & Format(intX)) = Format(CDate(rstReport(intX - 1).Name), "mmm yy") & " Act"
Else
Me("Head" & Format(intX)) = Format(CDate(rstReport(intX - 1).Name), "mmm yy") & " Fcst"
End If
Next intX

' Make next available text box Totals heading.
Me("Head" & Format(intColumnCount + 1)) = "12 Month Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" & Format(intX)).Visible = False
Next intX

End Sub
Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 4 (first text box with crosstab value).
For intX = 4 To intColumnCount
Me("Tot" & Format(intX)) = lngRgColumnTotal(intX)
Next intX
' Put grand total in text box in report footer.
Me("Tot" & Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" & Format(intX)).Visible = False
Next intX

End Sub
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 4 To intColumnCount
' Starting at column 4 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" & Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" & Format(intX))
' Get Avg of data columns from function
lngRgColumnAvg(intX) = YearAvg
Next intX


' Put row total in text box in the "Detail" section.
Me("Col" & Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub
Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" & Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" & Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub



Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef


' Set database variable to current database.
Set dbsReport = CurrentDb

' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("tblADA_Crosstab")


' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub
Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub

Private Function YearAvg() As Double
Dim intX As Integer
For intX = 4 To intColumnCount
lngRgColumnAvg(intX) = DAvg(lngRgColumnTotal(intX), rstReport)
Next intX
End Function