{Solved} Form Control Syntax for Averaging mulitple fields

[Del Cagle]

Developing Quality Mangement DataBase MS Access.
Have a form, displays 10 Fields representing 10 different measurements 10 different times a 1 thru 10 on same form drawing from 10 fields in table.
I need to AVERAGE these 10 controls, but cannot get syntax correct.
Can this even be done?
Concept Operator fills in the fields and it averages for him.
Please Help

[NeoPa]

Have a label field on the form which is updated in the OnCurrent event of the form

Private Sub FormName_OnCurrent(...)
	lblAvg.Caption = Format(( _
		Val(txtFld1) + _
		Val(txtFld2) + _
		Val(txtFld3) + _
		Val(txtFld4) + _
		Val(txtFld5) + _
		Val(txtFld6) + _
		Val(txtFld7) + _
		Val(txtFld8) + _
		Val(txtFld9) + _
		Val(txtFld10)) / 10, '#,##0.00')
End Sub

You'll have to replace the names I've used with your own.

[Del Cagle]

I am getting closer.
Ok Form name = Rockwell CHECK SHEET
Form Contains Text Boxes; Batch #1, Batch #2, Batch #3.... to 10
Tried to use your code exactly, but run into constant compile errors, they are very general, and I cannot fix it if I do not know what is wrong.

[NeoPa]

Firstly, you must create a label control on your form called lblAvg.

Private Sub Form_OnCurrent()
	lblAvg.Caption = Format(( _
		Val([Batch #1]) + _
		Val([Batch #2]) + _
		Val([Batch #3]) + _
		Val([Batch #4]) + _
		Val([Batch #5]) + _
		Val([Batch #6]) + _
		Val([Batch #7]) + _
		Val([Batch #8]) + _
		Val([Batch #9]) + _
		Val([Batch #10])) / 10, '#,##0.00')
End Sub

This code should now work for you. Luckily enough we didn't have to use the form name.

[Del Cagle]

Ok, too much time wasted on "compile errors" which do not really explaain where it is wrong.
Different approach.
I now need these 10 different Text Boxs Counted if they are filled Returning a number 1 thru 10
I can add them up =Nz+Field 1-10 in one field No Problem
I can Divide by 10 in another field. No Problem
What is needed is a divide by "Count" not 10 to return true average of "filled fields"
Currently the syntax in expression "Count" returns all filled fields "Batch #1" only Not what I need.
If 1, 2, 3 are filled I need it to return a 3
Please send syntax for this expression, in the meantime I will continue to trial and error
Thank you

[MMcCarthy]

Create a command button to control this operation so that the user prompts it when boxes are filled appropriately. I'll call it cmdCalc for now and the new textbox on the form to hold the average txtAverage.

 
Private Sub cmdCalc_Click()
Dim tempTotal As Double
Dim tempVal As Double
Dim iCount As Integer
Dim i As Integer
	tempTotal = 0
	iCount = 0
	For i = 1 To 10
		tempVal = Nz(Me.Controls("Batch#" & i), 0)
		If tempVal <> 0 Then
			tempTotal = tempTotal + tempVal
			iCount = iCount + 1
		End If
	Next i
	Me.txtAverage = tempTotal / iCount
 
End Sub

[Killer42]

Developing Quality Mangement DataBase ...

Sorry, but did anyone else spot the irony here? :)

[MMcCarthy]

Sorry, but did anyone else spot the irony here? :)

Is this the irony of quality and management by any chance or are you seeing another level of irony?

Mary

[MMcCarthy]

Is this the irony of quality and management by any chance or are you seeing another level of irony?

Mary

Sorry just saw it. (Ha Ha)

BTW, Del this is not directed at you. It just struck a funny bone.

Mary

[Del Cagle]

It's cool Mary, I am only "trying" to develope it, I am niether Quality, nor Managment. I am the classic Dilbert, and I have a pointy haired boss. I would swear Scott Adams is following me around taking notes.

[Tanis]

That's fine if you always have 10 values to average. Here is a function I use to average any amount. I use this in my Quality System.

Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() will average all the numeric arguments passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varAr g) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

[Del Cagle]

compile error (Method or data member not found)
txtAverage =
???? not winning at this

[MMcCarthy]

compile error (Method or data member not found)
txtAverage =
???? not winning at this

txtAverage was just the name I gave to the control(field) on your form where the average figure would be shown (or stored). Since I didn't know what it was I called it txtAverage. Just change it to whatever is the name of that textbox on the form.

Mary

[Del Cagle]

Bingo,
That's the ticket.
Thank you so much
Took alittle tweaking but it does work the way I need.
Time to shut the pointy haired one down LOL
I tried it on a copy of the database,
Now to apply it to the original
Thank you again :)

[Killer42]

It's cool Mary, I am only "trying" to develope it, I am niether Quality, nor Managment. I am the classic Dilbert, and I have a pointy haired boss. I would swear Scott Adams is following me around taking notes.

Glad you didn't take it personally. I was just pointing out the subtle humour in Quality Management being mis-spelled. :)