Subform Footer Calculation

[bsc0617]

I am trying to create a calculated footer control in a subform that will be displayed on the mainform. The footer control is a sum of a calculated control in the detail section of the subform. When viewing the subform in form view or datasheet view, it returns #Name?, but works fine when you open the mainform. I assume this is because the calculated control in the detail section of the subform references controls in the mainform. If my assumption is correct, I don't really expect to see it display correctly in the footer of the subform. However, when I point to the subform footer control in the mainform, it returns #Error. I have been stuck on this for quite some time trying various things. Below is the formula used in the detail section of the subform.


=IIf(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIf([ExpenseType]="F",[ANOI],IIf([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST])))

It's name is sfEST.



In the footer, I have tried =sum([sfEST]). That didn't work.

I have also tried =sum(IIf(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIf([ExpenseType]="F",[ANOI],IIf([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST])))), and also =sum(VAL(If....

And by the way, this control causes the other two footer controls to also return #error. If I remove this one, the other two are fine.

Can anyone help please???

[ssanfu]

When viewing the subform in form view or datasheet view, it returns #Name?, but works fine when you open the mainform. I assume this is because the calculated control in the detail section of the subform references controls in the mainform.

Correct!

Below is the formula used in the detail section of the subform.

=IIf(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIf([ExpenseType]="F",[ANOI],IIf([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST])))

What is the result if [ExpenseType] is "S"?
Be aware there is a missing the FALSE clause in the third nested IIF() statement. IIRC, all parts of the IIF() function is evaluated.

I would have this formula in the sub form query, not in a control.
Then bind the control to the field.


Here is how I build nested IIF() statements:

'IIF() statenemt syntax
=IIF(cond,TRUE,FALSE)

'add the condition
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,TRUE,FALSE)

'add result if true
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",FALSE)

'add result if false - another IIF() stattement
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF(cond,TRUE,FALSE))

'add second condition to nested IIF()
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF([ExpenseType]="F",TRUE,FALSE))

'add second result if 2nd nested IIF() is true
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF([ExpenseType]="F",[ANOI],FALSE))

'add second result if 2nd nested IIF() false - another IIF() stattement
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF([ExpenseType]="F",[ANOI],IIF(cond,TRUE,FALSE)))

'add third condition to third nested IIF()
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF([ExpenseType]="F",[ANOI],IIF([ExpenseType]="V",TRUE,FALSE)))

'add third result if 3rd nested IIF() is true
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF([ExpenseType]="F",[ANOI],IIF([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST],FALSE)))

'third result if 3rd nested IIF() is false
=IIF(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIF([ExpenseType]="F",[ANOI],IIF([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST],FALSE)))

It's name is sfEST.

What is named "sfEST"? A control or the subform?

[June7]

Aggregate functions cannot reference calculated controls. Must reference fields.

[bsc0617]

sfEST is the name assigned to the control that has that nested if statement. For the false part of the statement, it should be null if it doesn't meet any of the criteria in the IF statements.

[ssanfu]

For the false part of the statement, it should be null if it doesn't meet any of the criteria in the IF statements.

Then in my example, replace FALSE with "".

sfEST is the name assigned to the control that has that nested if statement.

I was confused because the prefix "sf" usually means sub-form.


@June
So if the calculation was in the query, the sum in the footer would work??

[bsc0617]

Yes, sf is for subform. I named all the controls that I needed to point to in the mainform with the prefix of sf. In this case, I thought I was going to be able to use =Sum([sfEst]) in the footer of that subform, but it didn't work.

So... I have fixed the false part of the 3rd IIF statement, but that doesn't resolve my problem in the footer. The footer control in the subform, which is...

=sum(IIf(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if >0",IIf([ExpenseType]="F",[ANOI],IIf([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST],"")))

has been assigned the name "Text64" for the moment. I went to the main form and entered =[sf_CL_ExpSubform].[Form]![Text64]. It still returns "#error and causes the other two subform footer fields that are pulled into the mainform, to also return "error.

Thanks for your help!

[ssanfu]

In the query for the sub-form, add a new column. Name it something like "calcEST". The column should look like:

calcEST:IIf(IsNull([ExpenseType]) And [ANOI]<>0,"Enter F or V if  >0",IIf([ExpenseType]="F",[ANOI],IIf([ExpenseType]="V",([ANOI]/[Forms]![f_CL_LoanRecordsForm]![mfEGIANOI])*[Forms]![f_CL_LoanRecordsForm]![mfEGIST],""))

In the footer, try:

=Sum(calcEST)

[June7]

Yes, calculated field in query is a field the aggregate function in textbox expression can reference.

[bsc0617]

I don't think I can do that. Based on past experience with this particular database, when my form uses a query that contains calculations, it will not let me update the fields in the form. Also, the long "IF" statement, contains controls that are also calculations (i.e. mfEGIANOI and mfEGIST(mf is the prefix for controls that are on the mainform, which are also calculations). So adding it to the subform query, would require me to add the other calculated controls to the query as well. I'm afraid I will be back where I started and not be able to update my form. Is that correct or am I missing something?

[June7]

It is possible to have calculated fields in query and still have an editable query.

Sounds like you are doing a lot of calcs that are best done on report. Ideally, a form is used to input raw data without regard to aggregate calcs. Queries and reports are intended to manipulated data with calculations.

[bsc0617]

Yes, I have them as a report as well. No problems there. The report uses a query that already has most everything calculated.

I changed the query behind the form to use a query that has only the fields and calcs I need for this subform. However, it is not an editable query and therefore, will not work. How do I make it editable?

As a work around to this problem, I added a 3rd subform to my mainform, that contains only the calculated control I need, then pointed to that control in the mainform. It works fine, except when I update the form, that one control doesn't update until I leave the form and then go back to it, either by going to next or previous record, or closing and reopening the form. All other calc fields update immediately upon leaving the changed field or row. I don't know how to write code or program for an event, like "after update" or something. Any suggestions?

[June7]

Doing something I've never had to. Maybe in the subform AfterUpdate event can run Refresh action.

[bsc0617]

Well, I'm stuck. I haven't been able to resolve this issue yet. I've tried adding the calc to the query, but then can't edit the form. I've tried the after update, and it isn't working there either. However, I may be doing this step wrong. Still trying!!! If anyone has other ideas, I'd really appreciate the help.

[bsc0617]

GOT IT. Refresh wasn't working. I used an event procedure to do a Requery ON EXIT of the subform, which would be after the updates are done.

[bsc0617]

I would rather have been able to get this work like all the others where it would total in the footer of the subform. That way, it would be update after each line is updated. So if anyone has suggestions, I would appreciate it. Thanks!