Calculate Average of 3 textoxes

[waq963]

Hi, How would i calculate the average of the values in 3 textboxes to be displayd in a 'Average' textbox? Thanks

 

[missinglinq]

1. Are all of the fields defined as Numbers in the underlying table.
2. Do the fields have zero defined as the Default Value?
3. Do you always want to divide by 3 to get the average? Or divide by the number of fields that are actually populated?

 

[waq963]

Hi, thanks for the reply.

1. All the fields are defined as Numbers
2. Zero is not a default value
3. I would like to divide by fields that are actually populated

 

[missinglinq]

There's probably a slicker way to do this, but this works, regardless of how many/how few of the textboxes are populated, or what order they're populated in:

Private Sub TextBox1_AfterUpdate()
Dim PopulatedFields As Integer
PopulateFields = 0

If Not IsNull(Me.TextBox1) Then PopulatedFields = PopulatedFields + 1
If Not IsNull(Me.TextBox2) Then PopulatedFields = PopulatedFields + 1
If Not IsNull(Me.TextBox3) Then PopulatedFields = PopulatedFields + 1

Me.TotalTextBox = (Nz(Me.TextBox1, 0) + Nz(Me.TextBox2, 0) + Nz(Me.TextBox3, 0)) / PopulatedFields

End Sub

Private Sub TextBox2_AfterUpdate()
Dim PopulatedFields As Integer
PopulateFields = 0

If Not IsNull(Me.TextBox1) Then PopulatedFields = PopulatedFields + 1
If Not IsNull(Me.TextBox2) Then PopulatedFields = PopulatedFields + 1
If Not IsNull(Me.TextBox3) Then PopulatedFields = PopulatedFields + 1

Me.TotalTextBox = (Nz(Me.TextBox1, 0) + Nz(Me.TextBox2, 0) + Nz(Me.TextBox3, 0)) / PopulatedFields

End Sub

Private Sub TextBox3_AfterUpdate()
Dim PopulatedFields As Integer
PopulateFields = 0

If Not IsNull(Me.TextBox1) Then PopulatedFields = PopulatedFields + 1
If Not IsNull(Me.TextBox2) Then PopulatedFields = PopulatedFields + 1
If Not IsNull(Me.TextBox3) Then PopulatedFields = PopulatedFields + 1

Me.TotalTextBox = (Nz(Me.TextBox1, 0) + Nz(Me.TextBox2, 0) + Nz(Me.TextBox3, 0)) / PopulatedFields

End Sub

 

[waq963]

Cheers Mate Worked Perfectly. I put the code behind button so it would only use the code once. Thanks alot it really helped me out.

 

[missinglinq]

Glad we could help!

 

[CEH]

Works good for 3 fields but wouldn't the code get pretty lengthy with....30...? Could you modify the code to use the "tag" property? If tagged add one to populated field... another tag to add and average the text fields.

 

[Poppa Smurf]

The following code will handle any number of textboxes used to store numbers to calculate the average.

I have attached a demo database that uses the code below.

Option Compare Database
' ***************************************
' calculate the averages of input fields
' the text boxes must have a prefix input_. If this is different then
' change the code UCase(Mid(ctl.Name, 1, 6)) = "INPUT_" to
' UCase(Mid(ctl.Name, 1, 6)) = "your prefix"
' text boxes with the prefix input_ e.g input_1, input_2 etc will be used to
' calculate the average. Textboxes with a NULL value are not included in the calculations
' This code cycles through the form and checks i the control is a textbox
' it then checks that the name of the textbox start with the prefix input_
' if name of the textbox start with the prefix input_ then the value of the textbox is added to the
' temp_total and the temp_count is incremented by 1
Sub calc_average_click()
Dim temp_total As Single
Dim temp_count As Integer
Set frm = Forms!frm_input

temp_total = 0
temp_count = 0

For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
ctl.SetFocus
If UCase(Mid(ctl.Name, 1, 6)) = "INPUT_" Then
If IsNull(ctl.Value) Then
Else
temp_total = temp_total + ctl.Value
temp_count = temp_count + 1
End If ' count
End If ' ctl is an input field
End If ' ctl is a text box
Next ctl
Me!form_count = temp_count
Me!form_total = temp_total
Me!form_average = temp_total / temp_count
End Sub

 

[missinglinq]

Works good for 3 fields but wouldn't the code get pretty lengthy with....30...

Sure, and we'd all be in trouble if pigs could fly! But the OP doesn't have 30 fields, he/she has 3! I generally stick to the simplest/clearest solution that will do the job for people posting on forums, taking into account their level of competency.

As my signature says, there's always more than one way to skin a cat! But I see no point in using 21 lines of somewhat cryptic code (cryptic to the OP) for a problem that can be solved, to the OP's satisfaction, with 6 lines of code that is self-explanatory.

If the OP needed to total and average 30 fields the response, obviously, would have been different.