Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    St Peter, Minnesota, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totaling Yes/No Data Type (Access 97 SR-2)

    I have a field that is a Yes/No checkbox and I would like to calculate the total of boxes checked Yes.

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

    Re: Totaling Yes/No Data Type (Access 97 SR-2)

    Say that your field is named Test. In a Totals query, you can use the expression Abs(Sum([Test])) to count the number of Yes values. On a form, you can put a text box in the form footer section with control source

    =Abs(Sum([Test]))

    Explanation: True corresponds to -1 and False to 0. When you sum the field, each Yes contributes -1; taking the absolute value removes the minus.

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    St Peter, Minnesota, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totaling Yes/No Data Type (Access 97 SR-2)

    Thank you Hans V for your response to my posting last week! I'm still having problems with my query, so let me expand a little more. I need to create a report that would total several Yes/No fields like LvnWill (living will), MealsOnWhls (meals on wheels), LifeLnRef (Lifeline Referral) that were check yes, based on a start date and a end date or between dates of a DDate (Discharge Date) field.

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

    Re: Totaling Yes/No Data Type (Access 97 SR-2)

    Will your report only include records with DDate between the start and end dates, or will it include other records too? If the former, you could put text boxes in the report footer section with control source

    =Abs(Sum([LvnWill]))
    =Abs(Sum([MealsOnWhls]))
    =Abs(Sum([LifeLnRef]))

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Location
    St Peter, Minnesota, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totaling Yes/No Data Type (Access 97 SR-2)

    Hi Hans

    I created a query using a Between statement based on the DDate field, I also included in the query 12 Yes/No fields. I then created a new report with the above query linked to it and included your suggestion of adding a text box for each of the Yes/No fields in the Page Footer with =Abs(Sum([field name])) in the Control Source. When I run the report I get '#Error' for the response.

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

    Re: Totaling Yes/No Data Type (Access 97 SR-2)

    As I wrote in my previous reply, you should place the text boxes in the report footer, not in the page footer. If you open the report in design view, you can simply select the text boxes and drag them from the page footer to the report footer.

Posting Permissions

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