I have four fields in my table that I want to average providing the fields have a value, not the null, for each case(id). I also have an average field in my table. I have a form for the table and I want the average value to be populated on click with the average of the four fields. How can I do this?
Average of Multiple Fields
Collapse
X
-
I'm sure theres a more efficient way of doing this but can't think of one at the moment.Code:Private Sub ButtonName_Click() Dim count As Integer Dim TotalValue count = 0 TotalValue = 0 If nz(Me![Field1],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field1] ElseIf nz(Me![Field2],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field2] ElseIf nz(Me![Field3],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field3] ElseIf nz(Me![Field4],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field4] End If Me!AvgValue = TotalValue / count End Sub
Mary -
Thanks Mary, but this doesn't work. It only reports the first Field value. I think it is because the code reads If Field 1 not null then count=1 and Total value=Field1 and ends there. Thus, if field 1 is empty, then it yields the field 2 value. Maybe I didn't explain myself right. I want it to average all the values, but only if the value is not null.
Here is the code with my variables, with mmcarthy's code.
Code:Private Sub AVERAGELE_Click() Dim count As Integer Dim TotalValue count = 0 TotalValue = 0 If Nz(Me.Ctl21st, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.Ctl21st ElseIf Nz(Me.AVS, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.AVS ElseIf Nz(Me.Fasano, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.Fasano ElseIf Nz(Me.EMSI, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.EMSI ElseIf Nz(Me.ISC, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.ISC End If Me.AVERAGELE = TotalValue / count End SubOriginally posted by mmccarthyI'm sure theres a more efficient way of doing this but can't think of one at the moment.Code:Private Sub ButtonName_Click() Dim count As Integer Dim TotalValue count = 0 TotalValue = 0 If nz(Me![Field1],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field1] ElseIf nz(Me![Field2],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field2] ElseIf nz(Me![Field3],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field3] ElseIf nz(Me![Field4],0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me![Field4] End If Me!AvgValue = TotalValue / count End Sub
MaryComment
-
Sorry I shouldn't do things when I'm tired. Try this ...
MaryCode:Private Sub AVERAGELE_Click() Dim count As Integer Dim TotalValue count = 0 TotalValue = 0 If Nz(Me.Ctl21st, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.Ctl21st End If If Nz(Me.AVS, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.AVS End If If Nz(Me.Fasano, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.Fasano End If If Nz(Me.EMSI, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.EMSI End If If Nz(Me.ISC, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.ISC End If Me.AVERAGELE = TotalValue / count End SubComment
-
Hhahaha, I ended up figuring it out and doing the same thing as you.
Originally posted by mmccarthySorry I shouldn't do things when I'm tired. Try this ...
MaryCode:Private Sub AVERAGELE_Click() Dim count As Integer Dim TotalValue count = 0 TotalValue = 0 If Nz(Me.Ctl21st, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.Ctl21st End If If Nz(Me.AVS, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.AVS End If If Nz(Me.Fasano, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.Fasano End If If Nz(Me.EMSI, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.EMSI End If If Nz(Me.ISC, 0) <> 0 Then count = count + 1 TotalValue = TotalValue + Me.ISC End If Me.AVERAGELE = TotalValue / count End SubComment
Comment