Calculations in Forms with Restrictions

[kellem80]

Hi,
I have a form with several subforms (using tabs). in each of the subforms for multiple fields I have calculations summing the fields in footer sections. These are then brought into the footer of the main form, so the user can see up to date information on the master record (for lack of better term) at a glance. the problem is, I just realized that one of my calculations is bringing in more than I want. I need to restrict the expression based on another field. I tried IIF (see below) but that just gave me errors. Any ideas? I even tried linking to a query field that has the limitation but no go. errors ensued.

Original Calculation:=Nz(Sum[MYFIELD1]),0

trying using IIF:
=IIF([MYFIELD2]="Approved", Sum([MYFIELD1]),0)

The IIF statement "works" (as in, no #error#), except it doesn't apply a zero to the amounts that are not equal to approved, it just sums all MYFIELD1

Any help appreciated. this is Access 2007.
thanks!

 

[kellem80]

Original Calculation:=Nz(Sum[MYFIELD1]),0

trying using IIF:
=IIF([MYFIELD2]="Approved", Sum([MYFIELD1]),0)

The IIF statement "works" (as in, no #error#), except it doesn't apply a zero to the amounts that are not equal to approved, it just sums all MYFIELD1

Any help appreciated. this is Access 2007.
thanks!

Another note: When I move to the next record in the subform, it changes the amount in the mainform. In the subform, the next record has a value of "Closed" in MYFIELD2.
Still a problem, since now, the main form is dropping the approved amount from the first record. this is still in development, so there are only two records for testing.

All my other subforms are correctly calculating.

Any thoughts on how to limit the calculation or MYFIELD1 based on the value of MYFIELD 2 (regardless of what record the subform is on)???? I'm at a loss.