Calculated values in a form showing up in a table

[TMO]

I know you should not have fields that total the results of other fields
store in your table but how do I get the results to show on my reports or
queries. The totals appear on forms but not in reports or queries.

Thanks
TMO

 

[Ken Sheridan]

In queries you include computed columns using similar expressions to those
used for the controls in your form. In reports you use computed controls in
exactly the same way as in a form.

Say you have a control in form with a ControlSource of:

=([UnitPrice] * [Qty]) * ([TaxRate]+1)

to compute the gross price on the basis of the unit price, the quantity
ordered and the tax rate expressed as a fractional value, e.g. 0.175 for 17.5
per cent, then in a query you'd put the following in the 'field' row of a
blank column in the query design grid:

GrossPrice[UnitPrice] * [Qty]) * ([TaxRate]+1)

Ken Sheridan
Stafford, England

 

[fredg]

I know you should not have fields that total the results of other fields
store in your table but how do I get the results to show on my reports or
queries. The totals appear on forms but not in reports or queries.

Thanks
TMO

If the form control is using an expression to calculate a total value,
use the same expression in an unbound control on your report, i.e.
= [FieldA] + [FieldB], or add a new column in your query, i.e.
TotalSales:[FieldA] + [FieldB] and use [TotalSales] in your report.