Access report: Get sum of calculated text field-CodePudding

I have created an access report in order to allocate invoice amounts to three entities based on a weighting factor.

The invoice amout is stored in a database table while the allocation is calculated during the report creation in different text fields. Each text field (txt_amount_h1, txt_amount_h2, txt_amount_h3) contains a formular that checks the "Allocation Formula" (e.g. H1/H2) and allocates the amount to the entities based on the weighting factor.

Example: Code txt_amount_h2

=IF([ALLOCATION_FORMULAR]="H2" OR [ALLOCATION_FORMULAR]="H1/H2" Oder [ALLOCATION_FORMULAR]="H2/H3" Oder [ALLOCATION_FORMULAR]="H1/H2/H3";[INVOICE_AMOUNT]/[WEIGHTING_FACTOR];"")

The calculation works as expected. But I struggle to get the sum of the calculated text fields in the report footer. Does someone has an idea? how_to_sum_calc_fields

Many thanks gmn

I unsuccessfully tried to search for a possbility to sum up the amount that is calculated in the text fields.

CodePudding user response：

Learn distinction between fields and controls - subtle but important. You are really talking about textboxes - not text fields. Data controls such as textboxes can be bound to fields of various types or have an expression as ControlSource. You have textboxes with expressions, not "calculated fields". Fields (natural or calculated) are in tables and queries.

Aggregate functions (Sum, Count, Avg, etc.) must reference fields of report's RecordSource, not textbox controls. If a formula in a textbox named Total is =Quantity * Price and those are field names, the Sum would have to be =Sum(Quantity * Price) - not =Sum(Total).

Do calculations in a query (or even in table with Calculated type field) as much as possible, then aggregate function can reference name of calculated field. If query calculates Total: Quantity * Price then textbox expression of =Sum(Total) would work.