?
Solved

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

Posted on 2014-01-27
11
Medium Priority
?
582 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 39

Expert Comment

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

Open in new window

0
 
LVL 31

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 31

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 39

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 39

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 31

Accepted Solution

by:
hnasr earned 1500 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 31

Expert Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

649 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