Excellent group! I found the post on calculating average for a control source but it does not actual

[ratttman]

Hello,
I hope someday I can help but for now I am a newbie.
I have a table (not normalized) that has 5 sample fields and an
average field that self calculates itself with the following formula:
=(Nz([natop1])+Nz([natop2])+Nz([natop3])+nz([natop4])+nz([natop5]))/
(Abs(Not IsNull([natop1]))+Abs(Not IsNull([natop2]))+Abs(Not
IsNull([natop3]))+Abs(Not IsNull([natop4]))+Abs(Not IsNull([natop5])))

This formula works great! The problem is that the table does not
capture the calculation? I see the correct answer in the form but when
I enter data via the form and then open the table the data is not
there for the average field. I have the formula in the Data tab /
Control Source & in Default Value.

When I try to use the same formula in a macro and populate based on an
event ( like :
Private Sub NaTop1_Change()
NaTopAvg.Value = (Nz([NaTop1]) + Nz([NaTop2]) + Nz([NaTop3]) +
Nz([NaTop4]) + Nz([NaTop5])) / (Abs(Not IsNull([NaTop1])) + Abs(Not
IsNull([NaTop2])) + Abs(Not IsNull([NaTop3])) + Abs(Not
IsNull([NaTop4])) + Abs(Not IsNull([NaTop5])))
End Sub

I get an error.
How can I get the data to show up in the table?
Thanks for any help.
rattman

 

[Rick B]

You don't. Storing the data that makes up the calculation and also storing
the calculated result is redundant. When you need that calculation, simply
perform it in your forms, reports, or queries using the formula you
provided.