Summing in the footer (97)

Topic

New Reply

I have run across an unusual reporting challenge. Among seven fields in the detail section of a report are a check box control and a text control. The checkbox (chkPersonal) source is:
=IIf([I_logPersonal]=True And [I_logVacation]=True,-1,0). This works fine, and does in fact result in a check when appropriate and no check when appropriate.

I want to count the number of checks in the report footer, and use the control “txtSumOfchkPersonal” with the source as:
=Abs(Sum([chkPersonal]))
When I run the report I am prompted with “Enter Parameter Value” “chkPersonal”. The control in the footer is not recognizing the name of the control in the detail section, or at least it seems not to.

I have a similar issue with the text control (intDays) in the details section, where the control source is:
=IIf([intDays]<81,[intDays],80). This works great, showing the number of days for each record. If there are less than 81 days, then it gives me the value, and if it exceeds 80, it gives me 80, which is the maximum allowable.

In the footer I use txtSumOfDays to capture the total number of days. If I use the following as the source:
=Sum([txtSumOfDays]), it again asks for a parameter for txtSumOfDays. If I use the following as the source:
=Sum(IIf([I_intCalendarDaysLost]<181,[I_intCalendarDaysLost],180)) it does result in the correct total.

Is there a switch or something I am not seeing? How can I get the footer controls to recognize the names in the detail section?

Thanks in advance for sharing your ideas.

Reply | Quote

Replies

I agree it may look as though it’s nonsensical, at least it’s consistent with form behaviour and there’s an easy solution.

If there’s a reason why the calculations must be performed in the report itself, then the solution is to make the control source of txtSumOfchkPersonal to be =Abs(Sum(IIf([I_logPersonal]=True And [I_logVacation]=True,-1,0))) and do something similar for txtSumOfDays.

An alternative, and possibly better, approach would be to make the report’s record source into a query (if it isn’t already) and and perform the calculations in there. Then the resulting calculated values will appear to the report as fields and you can use them directly in the detail section and sum them in the footer – aggregate function like Sum should work fine if they’re based directly on fields.

By the way, if l_logPersonal and l_logVacation are both boolean (Yes/No) fields, rather than use IIf, you can just use =(l_logPersonal AND l_logVacation) and this will result in a True (i.e. -1) result if both are true.

Reply | Quote

Simon,

Thanks. I had not thought of the alternative to IIF for the two logical fields. duh.

Do you have a suggestion for summing the following:
=IIf(([M_logPersonal]=False And [M_logVacation]=True And [chkFMLA]=False And [M_logRestrictedDuty]=True) Or ([M_logPersonal]=False And [chkFMLA]=False And [M_logVacation]=True And [M_logOverAge]=True),True,False)

NOTE: items prefaced with “M_” are fields in the query, items prefaced with “chk” are controls in the detail section of the report

Reply | Quote

Simon,

Thanks. I had not thought of the alternative to IIF for the two logical fields. duh.

Do you have a suggestion for summing the following:
=IIf(([M_logPersonal]=False And [M_logVacation]=True And [chkFMLA]=False And [M_logRestrictedDuty]=True) Or ([M_logPersonal]=False And [chkFMLA]=False And [M_logVacation]=True And [M_logOverAge]=True),True,False)

NOTE: items prefaced with “M_” are fields in the query, items prefaced with “chk” are controls in the detail section of the report

Reply | Quote

I agree it may look as though it’s nonsensical, at least it’s consistent with form behaviour and there’s an easy solution.

If there’s a reason why the calculations must be performed in the report itself, then the solution is to make the control source of txtSumOfchkPersonal to be =Abs(Sum(IIf([I_logPersonal]=True And [I_logVacation]=True,-1,0))) and do something similar for txtSumOfDays.

An alternative, and possibly better, approach would be to make the report’s record source into a query (if it isn’t already) and and perform the calculations in there. Then the resulting calculated values will appear to the report as fields and you can use them directly in the detail section and sum them in the footer – aggregate function like Sum should work fine if they’re based directly on fields.

By the way, if l_logPersonal and l_logVacation are both boolean (Yes/No) fields, rather than use IIf, you can just use =(l_logPersonal AND l_logVacation) and this will result in a True (i.e. -1) result if both are true.

Reply | Quote