Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Sum If from subform based on criteria in main form

Copper Contributor

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!

1 Reply

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