Dynamically set a report field based on a crosstab query, and count the number of records
Posted on 2014-01-27
I am dynamically setting the control sources for report fields based on the results of a crosstab query. It works, but I also need to have a total for each column that will give me the count of the number of records that have data in them (a date). So for example if there were 5 records and 3 of them had dates in the first column, I would see "3" at the bottom of that column in the "SumTxt1" field.
How do I modify this code so that the "SumTxt" fields show the count instead of just showing the contents of the field above?
Private Sub Report_Open(Cancel As Integer)
Set db = CurrentDb()
j = 1
Set rs = db.OpenRecordset("TempMatrix_CROSSTAB")
If j <= 10 Then
For i = 6 To rs.Fields.Count - 1
Me("lbl" & (j)).Caption = rs(i).Name
Me("txt" & (j)).ControlSource = rs(i).Name
Me("sumTxt" & (j)).ControlSource = rs(i).Name
j = j + 1