Nagesh
Member
- Local time
- Today, 05:24
- Joined
- May 10, 2020
- Messages
- 31
I am trying to create a report out of crosstab query where ;
a. Person as row headings
b. Category as column heading
c. Sum of Amount as value
d. and to obtain Sum (by row), Sum of amount as Row heading
Report generated from this crosstab query, is putting the 'Total' (row sum) in the second column rather than the last column (see attachments)
In the Report_Open -- > I have used recordset in vba to auto-populate the columns based on the number of Categories
Need help with the code to force Total (sum of amount) in the last column
a. Person as row headings
b. Category as column heading
c. Sum of Amount as value
d. and to obtain Sum (by row), Sum of amount as Row heading
Report generated from this crosstab query, is putting the 'Total' (row sum) in the second column rather than the last column (see attachments)
In the Report_Open -- > I have used recordset in vba to auto-populate the columns based on the number of Categories
Need help with the code to force Total (sum of amount) in the last column
Code:
Private Sub Report_Open(Cancel As Integer)
Dim i As Integer
Dim StrName As String
On Error Resume Next
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter
Set qdf = CurrentDb.QueryDefs("qryCrossTab")
For Each prm In qdf.Parameters
prm = Eval(prm.name)
Next prm
Set rst = qdf.OpenRecordset
intcolCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count
rst.Fields!Total.OrdinalPosition = intcolCount
If intControlCount < intcolCount Then
intcolCount = intControlCount
End If
' Fill in information for the necessary controls.
For i = 4 To intcolCount
StrName = rst.Fields(i - 1).name
Strval = rst.Fields(i - 1).Value
Me.Controls("lblPgHdr" & i).Caption = StrName
Me.Controls("tbxData" & i).ControlSource = StrName
Next i
' Close the recordset.
rst.Close
End Sub