Results 1 to 6 of 6
  1. #1
    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. #2
    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. #3
    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?

    I appreciate your response, regardless!

  4. #4
    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. #5
    Join Date
    Oct 2009
    Posts
    3
    That's still returning all records. Would multiplying by -1 do anything differently?

  6. #6
    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]))

    Add this to your query and then filter for >1.

Posting Permissions

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