Calculated Fields in a Form

[rubyred]

I have a form that has among many other controls, 5 controls that will have numerical data entered into them. Then the succeeding control on the form should add the 5 controls and then divide by the count of the controls that have a value in them. Most of the time all five controls will have a value so you would divide by 5, but there may be an occasional exception where one or two controls will not have a value.
So, I need to account for this possibility in my calculated total control.

What I need is coding to add the 5 controls and then divide by the COUNT of the controls that have a value.

Can anyone help me with this? I would be very grateful.

 

[doulostheou]

I don't know how you would go about this with something you could simply put in the control source. I do know how you could write this in visual basic. Let's say your last control is named Total and your text boxes are txt1,txt2, etc. Place the following code behind whichever event you want to trigger the update of the Total.

Dim txtCount as Integer

txtCount = 0
If Not isnull(txt1) Then txtCount = txtCount + 1
If Not isnull(txt2) Then txtCount = txtCount + 1
If Not isnull(txt3) Then txtCount = txtCount + 1
If Not isnull(txt4) Then txtCount = txtCount + 1
If Not isnull(txt5) Then txtCount = txtCount + 1

Total = (txt1 + txt2 + txt3 + txt4 + txt5)/txtCount

I haven't tested it but that should do it.


[This message has been edited by doulostheou (edited 05-09-2002).]

 

[rubyred]

Thanks so much for your quick response.

I applied the code to the calculated control in the On Got Focus Event and the results are as follows. When there is a value in all 5 controls, the calculated control gives the correct answer. However, if I leave one of the controls blank, the calculated control has no value. If I put a zero in the control, the calculated control gives an incorrect answer; for example, I entered 10 - 10 - 0 - 10 - 10 and the calculated control gave me an answer of 8 which in 40/5, versus 40/4, as the one control has no value.

What can I do to get the correct answer? Thanks in advance for your assistance.

 

[Fizzio]

This is the correct answer as you have put a value in the field (albeit zero). The code doulostheou gave you ignores null values not zeroes as 0 is a value!

Try changing this line
Total = (txt1 + txt2 + txt3 + txt4 + txt5)/txtCount to
Total = (Nz(txt1) + Nz(txt2) + Nz(txt3) + Nz(txt4) + Nz(txt5))/txtCount and see if that cures the blank field problem
HTH

 

[rubyred]

Thanks Fizzio!

That solved 1/2 of the problem. However, the txtCount is adding up to 5 - even when there are only 4 entries, thus giving me the wrong average of the 5 controls.

Any suggestions?

Does anyone know what is wrong?

Thanks in advance. . .

 

[rubyred]

Never mind everyone, the problem is solved. Thanks for your inputs! Greatly appreciated. Basically, what I did was code:

If IsNull [Text1] Or [Text1] = 0 Then
txtCount = txtCount
Else: txtCount = txtCount +1
End If

etc, for the rest of the controls, then I used the Nz function in the totals control.

Thanks again! Couldn't have done it without your help!