Solved

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

Posted on 2014-01-27
11
565 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 36

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
Technology Partners: 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!

 
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 36

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 36

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

749 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