Calculating averages on a form

  • Thread starter Thread starter JCA
  • Start date Start date
J

JCA

I have a series of fields on a form, and wish to calculate the average value
and display this in an unbound text box. I've calculated the value using the
expression builder (e.g. default value=([field1]+[field2]+[field3])/3), but
I'm having a series of problems:

1. How do I control how the value is displayed? Despite setting the decimal
places property to 0, the value is showing with multiple decimal places.

2. How do I enable 'live' calculation? The value doesn't appear immediately
(sometimes only after saving or clicking into a different database object).
Also the value being returned in the calculated field isn't updating if the
constituent fields are updated.

3. When I create a new record, the calculated field is pre-populated with
the value calculated for the previous record. Why isn't this blank?

I've obviously set this up wrong, but it seems such a simple thing to want
to do. Any advice will be greatly appreciated.
 
Replies in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


JCA said:
I have a series of fields on a form, and wish to calculate the average
value
and display this in an unbound text box. I've calculated the value using
the
expression builder (e.g. default value=([field1]+[field2]+[field3])/3),
but
I'm having a series of problems:

1. How do I control how the value is displayed? Despite setting the
decimal
places property to 0, the value is showing with multiple decimal places.
Click to expand...

Set the Format property to Fixed, as well as Decimal Places to zero.
(It may still show the fractions if this text box has focus.)

Alternatively:
=Round(([field1]+[field2]+[field3])/3), 0)
2. How do I enable 'live' calculation? The value doesn't appear
immediately
(sometimes only after saving or clicking into a different database
object).
Also the value being returned in the calculated field isn't updating if
the
constituent fields are updated.
Click to expand...

The calculated text box should update as soon as you finish entering a value
in a text box and move out of that box.
3. When I create a new record, the calculated field is pre-populated
with the value calculated for the previous record. Why isn't this blank?
Click to expand...

It should be blank (or the average of your default values) when you make the
new record row the current record.

If any one of the 3 values is Null (blank), the result will be blank.
I've obviously set this up wrong, but it seems such a simple thing to
want to do. Any advice will be greatly appreciated.
Click to expand...

Depending on what you're actually doing, it might be better to create
related table, where one record from this table can have several related
values. This may be much easier to manipulate.
 
JCA -

1. Set the format property of the control (unbound text box) to percent and
0 decimal places.

2. In the AfterUpdate event of each of the controls (for field1, field2,
etc.), do a Me.Repaint, which forces a recalculation of controls with
formulas like your text box.

3. In the OnCurrent event for the form, do a Me.Repaint.
 
You would do better to create a Totals query based on the table you are
working with and then use the Totals query as the record source of your
form. To create a totals query, first create a standard query. Then cl9ick
on the Sigma (looks like a capital E) button in th menu at the top of the
screen. For the fields you want to average, chanhe Group By to Avgerage.

Steve
(e-mail address removed)
 
Back
Top