Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Location
    California
    Posts
    2

    Unanswered: Sum records from 3 subforms that have Yes/No field checked?

    I have a Main Form that has three subforms and the Main Form does not have a related record source. I am able to filter all three subforms based on a combo box on the Main Form, but now I want to sum the records from each subform that have the Yes/No field checked and display the total on the Main Form. I have tried to create a text box on each subform that will sum only the checked records using IIF([Cleared]=Yes,Sum([Amount])), but this still adds the amounts for all records instead of only the records that are checked as Cleared. I tried to use the DSum function, but I seem to be missing something or it will not work for my situation-- DSum("Amount",Tbl_GNA42040","Cleared= Yes" And "Date Cleared Is Null"). I have also created queries that sum the amount if the Cleared box is checked and then I sum the total of the three queries, but if one is null or has no checked records I get no result (note: all three subforms will not always have checked records).
    Any ideas would be greatly appreciated! Let me know if you need more info.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Rather than this:

    IIF([Cleared]=Yes,Sum([Amount]))

    try

    Sum(IIF([Cleared]=Yes, [Amount], 0))

    The syntax for the DLookup would be

    DSum("Amount","Tbl_GNA42040","Cleared= Yes And IsNull([Date Cleared])")
    Paul

  3. #3
    Join Date
    Jun 2009
    Location
    California
    Posts
    2
    Quote Originally Posted by pbaldy
    Rather than this:

    IIF([Cleared]=Yes,Sum([Amount]))

    try

    Sum(IIF([Cleared]=Yes, [Amount], 0))

    The syntax for the DLookup would be

    DSum("Amount","Tbl_GNA42040","Cleared= Yes And IsNull([Date Cleared])")
    Thanks Paul!
    I eventually realized the same thing and I ended up using the Sum(IIF([Cleared]=Yes, [Amount],0)) to serve my overall purpose, but I am still interested in other ideas that people may have to fulfill my purpose, such as the use of VBA. If I am too vague in my description of what it is I am trying to do let me know so I can try to explain better.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Maybe a little more info would help, as I'm not clear on what's going on. Might just be a Friday afternoon brain cramp.

    This may be helpful:

    Forms: #Error when the Subform has no records
    Paul

Posting Permissions

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