Dynamically set a report field based on a crosstab query, and count the number of records

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
 Next i
 End If

 End Sub
Who is Participating?
hnasrConnect With a Mentor Commented:
If not resolved, upload a sample database.
 Me("sumTxt" & (j)).ControlSource = i -6

Open in new window

IS the code using 1 record of the record set?

Upload a sample database?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

MzLibertyAuthor Commented:
No, it gets columns 6 to 10 from the crosstab query. That part works, but right now my total row just repeats the first row because I can't figure out how to make it give me a count of the other field instead. If it were a normal report, my field would be [field1] and the total row would be count([field1]). How do I get the count from "rs(i).Name" ?
"repeats the first row"
This was the question.

Put rs.MoveNext in proper place.
MzLibertyAuthor Commented:
I don't want the next record, I want a count of the records. I have one field that gives me the data from one column of the crosstab query (txt1) and a field in the report footer to give me the count of records in that column (sumtxt1). I want sumtxt1 to be count([txt1]). But txt1= rs(I).Name.  I've tried = "Count(" & rs(I).Name & ")" but that just returns "[Count([MyFieldName])] which of course is not right. How do I get a count from rs(I).Name?
Did you try my suggestion?  It is using the index of the fields.  It excludes the first 6 fields just like your loop does.
MzLibertyAuthor Commented:
I don't need the count of the fields, I need the count of the data in the field.

My report needs:

                         training1     training2     training3
Person1          1/1/2014     4/1/2013
Person2        1/10/2014                          8/1/2013
                             2                    1                    1

I get the headings and the detail lines just fine, where the columns from the crosstab get inserted for the column headings. I just don't know how to get the bottom row, the count.

rs(I).Name gives me "1/1/2014" for the first record, but how do I get count(rs(I).Name)?
Create a footer section for the report or the report group.  Add a control for each column.  Use the following as the control source:



= .....
MzLibertyAuthor Commented:
But I don't know what fld1 is, so I can't do that. That's ok, I figured out another way to do it, I added a sub-report with the totals.
Please comment why did you select my comment as an answer.
All Courses

From novice to tech pro — start learning today.