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("TempMatr
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
rs.Close
End Sub
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("TempMatr
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
rs.Close
End Sub
IS the code using 1 record of the record set?
Upload a sample database?
Upload a sample database?
ASKER
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.
This was the question.
Put rs.MoveNext in proper place.
ASKER
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.
ASKER
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)?
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:
=Sum(abs(IsDate(fld1)))
=Sum(abs(IsDate(fld2)))
= .....
=Sum(abs(IsDate(fld1)))
=Sum(abs(IsDate(fld2)))
= .....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Open in new window