Results 1 to 5 of 5
  1. #1
    bfg is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    9

    Failing to get conditional count function to work on a report


    Hi,

    Going round in circles trying to create a Report which will count all boys ("M") who have a support date in the T1 column shown - under column T1 result should be 1 for boys and 2 for girls. Ive tried various =Count and nested Iif functions but simply cannot get my head around it. Any help much appreciated.

    Many thanks in anticipation.
    Attached Thumbnails Attached Thumbnails SampleAccess CountIf.JPG  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    try using the sum function instead

    =sum(not isnull(T1) * gender="M")

    note edited because forgot about gender

  3. #3
    bfg is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    9
    Ajax,

    thanks for suggestion ...

    =Sum(Not IsNull([T1])*[Gender]="M")

    producing "data type mismatch..."

  4. #4
    bfg is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    9
    Sorted... I think!

    =Sum(Not IsNull([T1]) And [Gender]="M")*-1

    Multiplied by -1 at end as resulting figure was negative value.

    Many thanks Ajax

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    no need to multiply just put a - before the sum

    =-Sum(Not IsNull([T1]) And [Gender]="M")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. count function on a report field is incorrect
    By cmiyatake in forum Database Design
    Replies: 3
    Last Post: 07-23-2019, 12:59 PM
  2. count function for report from query
    By Simonhtc4 in forum Queries
    Replies: 5
    Last Post: 03-20-2017, 08:40 AM
  3. Replies: 3
    Last Post: 01-13-2016, 12:34 PM
  4. Replies: 7
    Last Post: 10-04-2013, 11:45 AM
  5. Using conditional COUNT function
    By Laura WW in forum Reports
    Replies: 3
    Last Post: 11-08-2011, 11:59 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