creating a calculated control for average function

[mugziegumz via AccessMonster.com]

I'd like to create an AVERAGE function in a blank form. Is this possible? I
have 3 columns I need to average out. Performance, Attitude, Attendance. How
in a blank form do I enter in the calculation in the control source property
box?

In reference to the Access bible it's possible to create simple funcitons
such as:
Creating a Calculated Control
A calculated control displays a value that isn’t stored in the form’s
underlying Record Source. To
understand creation of a calculated control, you will now create one as
follows:
1. Select tblProducts in the Navigation Pane.
2. Click the Create tab on the ribbon, then click on the Blank Form command
in the
Form group to display a new form in Layout View.
3. Drag the Cost and SalePrice fields from the Field List onto the form.
4. Switch the form to Design View.
5. Click on the Text Box command in the Controls group and draw it on the
form.
6. Set the Name property to txtProfit.
The txt prefix means the control is a text box.
7. Set the Control Source property to =[SalePrice]-[Cost].
8. Change the Format property to Currency.
9. Change the Decimal Places property to 2.
10. Change the label’s Caption property to Profit:.
11. Switch to Form View to test the calculation.

 

[Dale Fye]

If you mean that you have three controls on your form, that corresponde to
[Performance], [Attitude], and [Attendance] fields in your record source,
then Yes, it is possible to Average these values, allthough you will have to
do the calculation yourself.

Create a new control (txt_Average) on your form. Set it's controls source
to :

= NZ([Performance], 0) + NZ([Attitude], 0) + NZ([Attendance]) / 3

Now if one of these doesn't have a value, this will still divide by 3, so
you might want to consider:

= NZ([Performance], 0) + NZ([Attitude], 0) + NZ([Attendance]) /
(iif(Isnull([Performance]), 0, 1) + iif(isnull([Attitude]), 0, 1) +
iif(Isnull([Attendance]), 0, 1))

The problem with this is that in a new record, these values will likely all
be blank (NULL) and doing a division by zero would give you an error in that
textbox.

So, instead of placing this in the Control Source of the textbox, you might
want to put it in the forms current event, where you could first check to see
if it is a new record, and if so, not do the calculation. If you do that,
you would also want to put the code in the AfterUpdate event of each of the
textboxes.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.