Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    New York, USA
    Thanked 0 Times in 0 Posts

    Conditional Counting (2000)

    In a Report, I want to count the number of records meeting conditions. I've placed a non-visible control [rinjcount] in the Details section of the report with the following expression:
    =IIf([ID_incidentcat]=2,([ID_typeincident]='1',1,0)) set to Running Sum=Over All and a control in the report footer with the expression =[rinjcount] It doesn't work.

    I want to count records where [ID_incidentcat] = 2 AND [ID_typeincident] = 1
    I'd also like to count records where [ID_incidentcat] = 2 AND [ID_typeincident] = 10 AND [ID_abusecat] = 3

    If no records match, I'd like it to show 0 (zero) on the report. Is an IIf statement the way to go?


  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Conditional Counting (2000)

    Unfortunately, Access does not have a "CountIf" function, like Excel. Recommend add calculated field to query used to populate report, with an expression that equates to True or False, such as:

    Expr1: [ID_incidentcat]=2 And [ID_typeincident]=1

    If expression equates to True, query will return -1, if False, 0. Repeat for any other T/F expressions you want to evaluate:

    Expr2: [ID_incidentcat]=2 And [ID_typeincident]=10 And [ID_abusecat]=3

    (You can add parentheses if necessary to clarify logic.) Add these fields to report's Detail section, as hidden textboxes. In report footer (or group footer if using grouping levels) add unbound textbox, set its ControlSource to something like:


    Since in VB/VBA True = -1 and False = 0, the sum will equal the number of records where expression is True. (Abs function returns absolute value - sign is ignored. If there are 10 True records, expression above will return 10 (not -10). There is no need for IIF unless you need to display results of T/F evaluation. If you just need a count then IIF is not necessary. Running Sum textboxes are very useful, but would not be used for this type of calculation.


Posting Permissions

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