# Thread: Calculated control to count from multiple fields?

1. Registered User
Join Date
Oct 2009
Posts
3

## Unanswered: Calculated control to count from multiple fields?

Hello--

I'm creating a report based on a table which has Yes/No fields for six options. I'm trying to write an expression that will count how many records have more than one of the six, in any combination. What I've got is

=Count(IIF([Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6]>1,1,0),0)

which is returning all records. I'm fairly new to writing expressions, so I'm not sure how to get it to return a count of the records that have TRUE for more than one of the selected fields.

Thanks!
Last edited by jphennen; 10-09-09 at 13:20.

2. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
You are on the right track. But to get the Yes/No fields converted to numbers you might want to try CInt to convert them to integers. In Access, True is -1 and False is 0. So you have to add up the Trues and get the Absolute Value with ABS (or multiple it by -1). Here is what I came up with:

=Count(IIF(Abs(CInt([Field1])+CInt([Field2])+CInt([Field3])+CInt([Field4])+CInt([Field5])+CInt([Field6]))>1,1,0),0)

3. Registered User
Join Date
Oct 2009
Posts
3
That pops up an error message saying "The expression you entered has a function containing the wrong number of arguments." Any idea how to fix that?

4. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
It is probably the ,0) at the end I thought it looked odd. I think it should be:

=Count(IIF(Abs(CInt([Field1])+CInt([Field2])+CInt([Field3])+CInt([Field4])+CInt([Field5])+CInt([Field6]))>1,1,0))

5. Registered User
Join Date
Oct 2009
Posts
3
That's still returning all records. Would multiplying by -1 do anything differently?

6. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
Take out the Count and create a calculated field and filter for >1. Similar to :

Checks:Abs(CInt([Field1])+CInt([Field2])+CInt([Field3])+CInt([Field4])+CInt([Field5])+CInt([Field6]))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•