1

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

Example: sample_allocation

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.

gmn
  • 13
  • 2
  • Your example shows function of If() but the correct function is IIf(). What is `Oder`? – June7 Nov 20 '22 at 17:54
  • @June7: That is _or_ in German. – Gustav Nov 20 '22 at 20:56
  • Okay, that would have been my guess but why mix English and German? And is If() valid in German Access? – June7 Nov 20 '22 at 20:57
  • Sorry for the mix up. The report and the field names are in german. I just translated the names in order to get a better understanding of the example. I also used "IIf()", however it was automatically transformed to "Wenn()" -> german word for "If()". – gmn Nov 28 '22 at 18:13

1 Answers1

1

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.

June7
  • 19,874
  • 8
  • 24
  • 34