I have a payslip main report with 3 subreports, all are linked through the employee's serialNumber:
I was able to show the total amounts of the 3 subreports on my main report's page footer. Since my last subreport rptPayslipOpay, may or may not have any data, I am displaying it in a text box named [txtOpay] like this:
=IIf([rptPayslipOPay].[Report].[HasData],[rptPayslipOPay].[Report]![sumOpay],"")
My problem is,the value of [txtOpay] shows even if the page has moved on to another employee, thus displaying the previous employee's supplementary payments to the next employee.
Is there a way to prevent this from happening?
Thank you very much.
You might be able to get it to work by fiddling around with the subreports, but assuming a 1:1 relationship between top-level report and subreport, and assuming serialNumber is a string; instead of your IIf
statement, you might consider trying something along the lines of:
=DLookup("sumOpay", [rptPayslipOPay].Report.RecordSource, "serialNumber='" & [serialNumber] & "'")
I'm not sure exactly how your objects are named, but I think this should be close to what you're looking for.
By your description, it sounds like "sumOpay" might be a calculated field in the rptPayslipOPay report. If so, you can use:
=DSum("<field containing data to sum>", [rptPayslipOPay].Report.RecordSource, "serialNumber='" & [serialNumber] & "'")
Granted, these will run more slowly, but this might be an easier way to get it to work.
Another option is to left-join rptPayslipOPay's data from in your top-level query, and sum it.