Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Chakota is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    9

    Using Count (llf) function on Form to Count Number of Specific Responses

    Have a large medical checklist DB for different specialty Peer Review and have in the past counted results manually. Each result has a differently named combo box with responses (Yes, No, NA). example below. I'm using an unbound field to count just the total "Yes" responses. To do this, I've tried using the below Count statement in the unbound field Control Source, but it's not working. Looking for assistance please on how to automatically count selected responses from different fields? I've also tried replacing Count with =Sum(llf( and ending with ,1,0)) instead of 1,Null)).






  2. #2
    Chakota is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    9
    Looks like what I pasted into the thread did not take, the count statement I'm using is:

    =Count(IIf([Results1]="Yes" Or [Results2]="Yes" Or [Results3]="Yes" Or [Results4]="Yes" Or [Results5]="Yes" Or [Results6]="Yes" Or [Results7]="Yes",1,Null))

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The attached db show one way to achieve your objective
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    I could use some help understanding one line of the VBA code in your YesCount() database:

    Code:
      If ctrl.Properties.Item("Tag") = "x" And Nz(ctrl, "") = "Yes" Then


    What is this segment of code doing? Where does “Tag” come from and why check to see if it is equal to x? What is x?
    Code:
      ctrl.Properties.Item("Tag") = "x"


    Also, what does this snippet mean? I understand the Nz function but I do not understand what this snippet of code is doing?
    Code:
      Nz(ctrl, "")

    Thanks.

    Steve

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Where does “Tag” come from and why check to see if it is equal to x? What is x?
    Each control has a property called "Tag". In the attached db I have set that Property to "x" for all the combo boxes that we want to test for a "Yes" value.
    I understand the Nz function but I do not understand what this snippet of code is doing?
    If the combo box has no value (is Null) the code testing to see if the value = "Yes" would fail but it works testing against a ZLS (""). We could use any value there other than "Yes".
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    So setting Tag=x is a way to avoid testing other combo boxes that might be on the form but are not to be included in the number-of-yes testing?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Seems to me ControlSource expression should work. What happens - #error, wrong result, nothing?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    That is correct

  9. #9
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    One more question if you would be so kind. Each combo box triggers a requery of txtTot. Combo box Result1 triggers the requery via the AfterUpdate event. The remaining five combo boxes trigger the requery via the Click event. Why does Result1 differ from the other five in which event triggers the requery?

    Sorry to nag so much

    Steve

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Why does Result1 differ from the other five in which event triggers the requery?
    Ha! Ha! Well spotted Steve. The answer is very simple.......I made a mistake . I intended to put the requery code in the After Update event of ALL the combo boxes.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    Chakota is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    9
    June7, putting it in the ContolSource expression gives a response of "1", no matter how many are selected as Yes.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I tested expression with textbox in form header and it works.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Chakota is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    9
    Not sure why the expression does now work in the ContolSource, but it counts the first one changed regardless of which of the 7 are answered Yes, but the count does not change with any additional values of Yes selected.

    =Count(IIf([Results1]="Yes" Or [Results2]="Yes" Or [Results3]="Yes" Or [Results4]="Yes" Or [Results5]="Yes" Or [Results6]="Yes" Or [Results7]="Yes",1,Null))

  14. #14
    Chakota is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    9
    Bob - Thanks for the database, and your solution worked beautifully, except it for a moment caused another dilemma. I'm running an audit program in the background of the database which captures the old and new value of a field, no matter how many times it's changed. Part of the code requires an entry in "TAG" Field. After thinking about it, I substituted "x" in your Public Function code for the value that I was already using in TAG and ;the Audit and Count functions both seem to be working.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Count() counts how many records meet the criteria, it does not count how many fields of record are "Yes". The criteria in the expression is that at least 1 field must be "Yes" to return value of 1.

    If you want to count fields then the VBA solution is probably best approach because an expression in ControlSource would be quite unwieldy.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-25-2019, 11:54 AM
  2. Replies: 6
    Last Post: 02-10-2017, 08:21 AM
  3. Replies: 5
    Last Post: 12-17-2014, 09:51 PM
  4. Count of specific number
    By samirmehta19 in forum Access
    Replies: 3
    Last Post: 05-20-2013, 02:29 PM
  5. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums