Solved

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

Posted on 2014-01-27
11
570 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 37

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 37

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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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