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.
DSum("Amount","Tbl_GNA42040","Cleared= Yes And IsNull([Date Cleared])")
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.