How to count multiple text boxes

[Brian62]

I have a report where I am trying to count all the text boxes which comprises of about 8o. I want to separate them by Yes, No and N/A. I tried a few different options and none have worked in Control Source.



Example1: =Count(IIf([RDStudyAuditSectionA.Doc #12-5]+[RDStudyAuditSectionA.Email Corrospondence]+[RDStudyAuditSectionA.Doc #2]="Yes",0))

Example2=Count(IIf([Doc #12-5]+[Email Corrospondence]+[Doc #2]="Yes",0))

Example3=Count(IIf([Doc #12-5],[Email Corrospondence],[Doc #2]="Yes",0))

The formula is set in the report footer. The information is coming from a query with 8 different tables linked to one query which the Report operates off of.

I have tried it on the query side but I can't figure it out there as well.

Thanks!

[weekend00]

I am not sure what do you want by:
[RDStudyAuditSectionA.Doc #12-5]+[RDStudyAuditSectionA.Email Corrospondence]+[RDStudyAuditSectionA.Doc #2]="Yes",

but you can use DCOUNT to get what you need.

this example counts records with [Doc #12-5]='YES' in table RDStudyAuditSectionA:
=dcount("*", "RDStudyAuditSectionA", "[Doc #12-5]='YES'")

[Brian62]

The thing with that is the text boxes are named differently. They are separate shown in rows in the report. This is an example what I had in an excel format.

The yes, No, and NA are dropdowns from the form. I would need to count each of the different entries through the report or query.

I really want this to show in percentages of the total fields if that is possible.

I hope you can make sence of this. Thanks!

A. (Initial/Annual Review) – purple sectionYes/No/NA
1. E-mail correspondence Yes
2. Application for Continuing IRB Review and Re-approval / Document # 2 (Required if > 1 Year) No
3. Continuation/Closure Checklist / Document # 12.5 (Required if > 1 Year) No
4. Initial Research Review Form/Document # 25 (Required) N/A
5. Response to Lapse / Document # 28.51 N/A
6. Lapse Letter template / Document # 28.5 Yes
7. Phoenix Research Proposal Submission Checklist/ Document # 30 (Required) No
8. Notification of Protocol Approval / Document #30.5 (Required) Yes
9. New Proposal Submission Checklist/ Document # 36 (Required) N/A

Total Yes: 3 Total No: 3 Total N/A: 3

[weekend00]

use Dcount, one dcount at a type, then get the total if needed.

[Brian62]

I don't believe that is possible since there are about 80 text boxes which are all different that represent Yes, No or N/A. I have to be able to calculate each Yes, No and N/A Separately.

Do you know another way or how to string them together.

I would need three Dcounts for each of the three criterias.

[weekend00]

I am not very clear if you want to count across rows or across columns or count across columns and rows.

example

col1 col2 col3 col4 col5
Y     N     n/a   Y    Y
N     N     Y    n/a   n/a

how do you want to count them?

[Brian62]

I want to add all the column names in the report to come out with my totals. Here is part of the database so you can see what I am talking about in the report.

I appreciate your help!!!