Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Only True Values (XP)

    Edited by HansV to avoid <!t>[Free]<!/t> from being turned into a smilie - used <!t>[t]<!/t> and <!t>[/t]<!/t> tags.

    Sorry to ask a dumb question, but I have a Report that Totals monies paid during a particular month for Meeting Rooms that have been hired. However, sometimes the meeting rooms don't attract a charge, so we have a True/False Field that indicates that it was given FREE.

    The Report is based on a Query that Totals these amounts, but the True/False Field has no criteria set against it, so every Record is returned, even if the Value is Null, ie, the FREE Field is True. As such, when I try to Count the TRUE/FALSE Field on my Report, I get a Total of ALL the Records, not just those where the FREE Field is True.

    How can I ask Access to only Count If True? Is it the IIF Function or the DCOUNT Function, and what would be the Syntax?
    This was what I was trying, but it was clearly wrong, however, it'll give you an idea of exactly what I'm try to achieve:
    =DCount("<!t>[Free]<!/t> = 'TRUE'")

    Thanks in advance for your help, and sorry for asking what I'm sure is an easy question.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Only True Values (XP)

    Since True = -1 and False = 0, the easiest way to count the number of records for which Free = True is to sum the values of Free and to take the absolute value:

    <code>=Abs(Sum(<!t>[Free]<!/t>))</code>

    BTW no need to apologize. The Lounge is for everyone, from beginners to experienced developers. And I'm sure others will benefit from this thread too.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Only True Values (XP)

    Thank You Hans both for the code and your kindness. [img]/forums/images/smilies/smile.gif[/img]

    Have a great night!

    Your explanation of how ABS works in this situation is a LOT clearer than Access' own Help! I never would have thought of doing that (obviously, says you)!

Posting Permissions

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