Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting records in an Access report (Access 2000)

    I want a count of the number of compliant records to appear in an text box at the end of a report. The code I am entering in the Expression Builder for the Control Source of the text box is
    =Count([qryCompliant]![Compliant]WHERE[qryCompliant]![Compliant] ="Yes")
    This counts the total number of records rather than just the "Yes" records. Does it not understand WHERE?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting records in an Access report (Access 2000)

    No, it doesn't. Count doesn't take criteria arguments, it just counts occurrences. The simplest way to do this is to create a calculated expression in your underlying query something like this:

    CompliantCount: [Compliant]="Yes"

    That will return either a -1 or a 0 as the value for CompliantCount. Then in your report, put this in the textbox controlsource:

    =Abs(Sum([CompliantCount]))

    This assumes that your query is a straight select query and not a group by query.
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting records in an Access report (Access 2000)

    Thanks Charlotte, this worked great! Once I had the Yes responses and the total, I used the text boxes as variables to compute the No's and the percent of the total represented by each.

Posting Permissions

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