Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41

    Unanswered: Conditional Totals

    I have a continuous form based on a query using 2 joined tables with a date criteria. Not a problem with this. In the form footer, I have a text box where I compute several totals using the sum function. Again, this is not the problem.

    The Problem: I would like to compute several totals similar to the one above but include only the records where a Yes/No field is false then put the results in text boxes, also in the form footer. I tried to use the sum function with a WHERE clause and with a HAVING clause but Access won’t accept it. I built a SQL statement just for that control and put it in the record source property of the text box that was to hold that total but Access mangled it (I tried several times and the result in the property sheet was only part of the query I typed in.

    Is there a way that I can do this? If anyone needs the SQL and/or the VBA, I’ll be glad to post it.

  2. #2
    Join Date
    Oct 2011
    Posts
    16
    A Text Box like this:

    =Sum(Iif(Checkbox=-1,Value,0))

  3. #3
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    I can't get "=Sum(Iif(Checkbox=-1,Value,0))" to work. It doesn't specify the txtBox, I'm trying to total (or not).

  4. #4
    Join Date
    Oct 2011
    Posts
    16
    Replace "value" with whatever it is you are trying to sum: Be it a field name or some other onject.

  5. #5
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    Before I read the responses, I decided to create a querydef using a SQL in VBA and then used the querydef as the recordsource for the form rather than using the SQL statement directly.

    Then I created a second SQL to sum the columns where the checkbox was false and created a recordset using the second SQL and posted the result into the text boxes (me!txtBox = rst.whateversum). It works and I’ll be able to understand it later if I make a change or something. Thanks for the help. As soon as I have time, I want to try summing with the iif function.

  6. #6
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    I tried the use of iif in the control source property and it worked to perfection. I have changed my form to reflect it and now my VBA procedure is about 20 lines shorter. Thanks a million.

  7. #7
    Join Date
    Oct 2011
    Posts
    16
    Excellent! Thanks for letting me know!

Posting Permissions

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