Solved

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

Posted on 2014-01-27
11
564 Views
Last Modified: 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
 Next i
 End If
 rs.Close

 End Sub
0
Comment
Question by:MzLiberty
  • 4
  • 4
  • 3
11 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 39812811
 Me("sumTxt" & (j)).ControlSource = i -6

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 39812921
IS the code using 1 record of the record set?

Upload a sample database?
0
 

Author Comment

by:MzLiberty
ID: 39813080
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" ?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 30

Expert Comment

by:hnasr
ID: 39813092
"repeats the first row"
This was the question.

Put rs.MoveNext in proper place.
0
 

Author Comment

by:MzLiberty
ID: 39813126
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?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39813186
Did you try my suggestion?  It is using the index of the fields.  It excludes the first 6 fields just like your loop does.
0
 

Author Comment

by:MzLiberty
ID: 39813260
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)?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39813294
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)))

= .....
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 39813412
If not resolved, upload a sample database.
0
 

Author Comment

by:MzLiberty
ID: 39813426
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39813449
Please comment why did you select my comment as an answer.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question