473,320 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

{Solved} Form Control Syntax for Averaging mulitple fields

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
Nov 9 '06 #1
14 1677
NeoPa
32,556 Expert Mod 16PB
Have a label field on the form which is updated in the OnCurrent event of the form
Expand|Select|Wrap|Line Numbers
  1. Private Sub FormName_OnCurrent(...)
  2.     lblAvg.Caption = Format(( _
  3.         Val(txtFld1) + _
  4.         Val(txtFld2) + _
  5.         Val(txtFld3) + _
  6.         Val(txtFld4) + _
  7.         Val(txtFld5) + _
  8.         Val(txtFld6) + _
  9.         Val(txtFld7) + _
  10.         Val(txtFld8) + _
  11.         Val(txtFld9) + _
  12.         Val(txtFld10)) / 10, '#,##0.00')
  13. End Sub
You'll have to replace the names I've used with your own.
Nov 9 '06 #2
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.
Nov 9 '06 #3
NeoPa
32,556 Expert Mod 16PB
Firstly, you must create a label control on your form called lblAvg.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_OnCurrent()
  2.     lblAvg.Caption = Format(( _
  3.         Val([Batch #1]) + _
  4.         Val([Batch #2]) + _
  5.         Val([Batch #3]) + _
  6.         Val([Batch #4]) + _
  7.         Val([Batch #5]) + _
  8.         Val([Batch #6]) + _
  9.         Val([Batch #7]) + _
  10.         Val([Batch #8]) + _
  11.         Val([Batch #9]) + _
  12.         Val([Batch #10])) / 10, '#,##0.00')
  13. End Sub
This code should now work for you. Luckily enough we didn't have to use the form name.
Nov 9 '06 #4
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
Nov 10 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdCalc_Click()
  3. Dim tempTotal As Double
  4. Dim tempVal As Double
  5. Dim iCount As Integer
  6. Dim i As Integer
  7.     tempTotal = 0
  8.     iCount = 0
  9.     For i = 1 To 10
  10.         tempVal = Nz(Me.Controls("Batch#" & i), 0)
  11.         If tempVal <> 0 Then
  12.             tempTotal = tempTotal + tempVal
  13.             iCount = iCount + 1
  14.         End If
  15.     Next i
  16.     Me.txtAverage = tempTotal / iCount
  17.  
  18. End Sub
  19.  
  20.  
  21.  
Nov 12 '06 #6
Killer42
8,435 Expert 8TB
Developing Quality Mangement DataBase ...
Sorry, but did anyone else spot the irony here? :)
Nov 12 '06 #7
MMcCarthy
14,534 Expert Mod 8TB
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
Nov 12 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
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
Nov 12 '06 #9
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.
Nov 13 '06 #10
Tanis
143 100+
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(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function
Nov 13 '06 #11
compile error (Method or data member not found)
txtAverage =
???? not winning at this
Nov 13 '06 #12
MMcCarthy
14,534 Expert Mod 8TB
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
Nov 13 '06 #13
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 :)
Nov 13 '06 #14
Killer42
8,435 Expert 8TB
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. :)
Nov 13 '06 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
3
by: Angelos Karantzalis | last post by:
Hi guys, I've a small problem with validators. I'm building a single .aspx file that handles all my form posts. I need to be using ASP.NET validators ( or subclasses thereof ), so what i do to...
2
by: Richard Lionheart | last post by:
Hi All, I generated a WebForm and created a Virtual Directory for it IIS. But I got an error message (shown below) saying something like my app lacked appropriate privileges. David Wang...
7
by: Chuck Anderson | last post by:
I'm pretty much a JavaScript novice. I'm good at learning by example and changing those examples to suit my needs. That said .... ..... I have some select fields in a form I created for a...
3
by: cyber0ne | last post by:
I'm designing a basic form for data entry into one main table. There are two fields in the table that I would like to be automatically populated, not user-entered, when the record is posted. ...
6
NeoPa
by: NeoPa | last post by:
Introduction The first thing to understand about Sub-Forms is that, to add a form onto another form takes a special Subform control. This Subform control acts as a container for the form that you...
2
by: dlevene | last post by:
SOLVED Hi all. I have a query that takes the value of a form control as the criteria for one of the fields: the records are all Contacts, and the form allows you to select the Contact Type. This...
19
by: postman | last post by:
I have a parent form bound to a recordsource and several controls are bound to fields in that recordsource. There are also several unbound controls that are used to display various data in a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.