Working with Microsoft Access Reports can be time consuming and fiddly to master and one of the common challenges is correctly writing calculated expressions between main reports and their embedded sub-reports.
If you are not familiar with writing expressions, then I suggest you start with the built in Expression Builder utility which will navigate and build the correct references.
With the Expression Builder, users can create a hierarchal reference if loaded via the main report design with a sub-report included (and loaded). Make sure you have created a summary calculation in the sub-report and it’s placed in the correct section too (namely the footer section).
Microsoft Access Reports – Passing Totals From A Subreport Into The Main Report
Placing for example the Sum function to total the ‘Order Amount‘ from a sub-report called ‘Orders‘ in the wrong section you throw you an error and only the footer section will do.
This will look something like: =Sum([Order Amount])
To help referencing at the top (main) level, it would be recommended that you give this new text box control a meaningful name like ‘txtOrderValue‘.
Now at the top level in the main report, adding a new text box control (don’t have to name this control), users can refer to the sub-report by using the following expression:
=[Orders].[Report].[txtOrderValue] or =[Orders].[Report]![txtOrderValue]
In fact, leaving out square brackets (for single ‘non-space’ words) will automatically wrap with square brackets when saving the control. The separator used between the type and control name (second part) can be either a period (.) or exclamation (!) but can not be used but not between the report name and type.
The structure (or syntax as it’s technically known) for a sub-report reference is:
=[Sub-Report Name].[Report]![Control Name]
With your Microsoft Access reports that typically use embedded sub-reports and the requirements to pass their totals across and up to the main report, you may want to further wrap and enhance your calculations to avoid empty or null errors which will be so if there are no records found or a record has a null value.
To help this along, you may also need to call the logical IIF function. Take a look at the following examples:
=IIf([Orders].[Report].[HasData], [Orders].[Report].[txtOrderValue], 0)
=IIf([Orders].[Report].[HasData], Nz([Orders].[Report].[txtOrderValue], 0), 0)
The ‘HasData‘ property yields a TRUE or FALSE value which is ideal for your IIf test. The second example also include the NZ function (meaning Null To Zero) defaulting to a zero value to handle null values gracefully.
Hopefully, you can start to see how easy and powerful your MS Access reports can be it.
If you want to know more about the correct approach to building Microsoft Access reports, why not take a look at my eBook about building reports.
Tags: access reports and subreports, calculating totals in ms access reports, Expression Builder, microsoft access reports, MS Access, working with subreports