Forum Discussion

LeahR1290's avatar
LeahR1290
Copper Contributor
Jan 09, 2024

Sum If from subform based on criteria in main form

Hello,

I am attempting to create a Sum If formula from my subform based on criteria in my main form (year). The year will change when the main form changes (based on ProjectCode), so I want to set the If criteria to a text box on the main form rather than a set value. 

 

I have tried =IIF(criteria, SUM(field), 0), =SUM(IIF(criteria, field to sum, 0)), and I have tried a DSUM formula using the table the subform is based on. I have been using the expression builder, and it doesn't seem to matter if I refer to the criteria on the mainform as Mainform!txtbox or simply as txtbox.

 

Is this even possible?

 

Thanks!

access
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Jan 10, 2024

    Hi,

     

    In order to understand the problem:

     

    1. Where do you try these expressions? In the Controlsource of a textbox in the subform?
    2. Is the year a criterion for whether the calculation should be performed at all, or should it filter the records for the total?
    3. What are the results you get with your attempts?
    4. Let us see the real expression without paraphrasing it. Only then we have a chance to see if the syntax is ok.

    Servus
    Karl
    ****************
    Access Forever
    Access News
    Access DevCon
    Access-Entwickler-Konferenz AEK

Resources