Formula in Form

[jlclark4]

Good Morning,

This one is a doosy and I have been struggling with it for weeks. I need to have a formula in my form (it can be unbound text box) that populates a average. Right now I have my form set up with tabs. In each tab is a set of questions. Each question has a option box of Yes, No, N/A; 1, 2, 3 respectivly. I have a code that states if option### is 1 then text### is 3; if option### is 2 then text### 0. This input a number (weight of the question) into another text box. I can uses a standard average formula in the text box at the end of all the questions (Add up the text boxes and divide by points possible) BUT if the user selects N/A (3) I do not want that to be involved in the average...how can I do this?!

J.

[maximus]

I have tried to replicate the problem in this simple example.

Assumptions:
1) There can be three possible Yes; No; N/A
2) The points for the answers will be Yes=3; No=0; N/A not to be included in the calculation.
3) Average calculated by the formula TotalPoints/Total No.of questions with out N/A.

Example: There are four questions 2 answers = Yes 1 answer = No 1 unswer = N/A then the total score of points = 3+3+0=6 and the average will be 6/3=2)



I have a standard module where I have Declared intnumber(1-4) Public variables these variables store the points based on the answer selected in the comboboxes. This Module all has a Function MyAverage which actually calculates the average.




Option Compare Database
Public intnumber1 As Integer
Public intnumber2 As Integer
Public intnumber3 As Integer
Public intnumber4 As Integer
Public intCounter As Integer
Option Explicit



Function MyAverage() As Double
Dim intSum As Integer
intSum = intnumber1 + intnumber2 + intnumber3 + intnumber4
If (intSum) > 0 Then
If intCounter > 0 Then
MyAverage = intSum / intCounter
Else
MyAverage = 0
End If
Else
MyAverage = 0
End If
End Function


This is a piece code which is attached to the Combo0 AfterUpdate event. similar Codes are also attached to each of the comboboxes.

How does the code work:

When you select answer the corresponding point for the answer is stored in the public Variable intNumber(1-4) depending upon the th combobox. then Aloop runs through all the comboboxes to dtermine the number of questions answered without a N/A. E.g. If you have answered only 2 with either Yes or No questions your intCounter (that is the total no.of questions answered with out N/A) will be 2, if you have selected N/A for one then intCounter will be 1. The looping ensures that you can change the answer and still get the corret average.


Private Sub Combo0_AfterUpdate()
Dim ctl As Control
If Me.Combo0 = "Yes" Then
intnumber1 = 3
ElseIf Me.Combo0 = "No" Then
intnumber1 = 0
End If

intCounter = 0
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox
If IsNull(ctl.Value) Then
intCounter = intCounter + 0
ElseIf ctl.Value = "Yes" Or ctl.Value = "No" Then
intCounter = intCounter + 1
Else
intCounter = intCounter + 0
End If
End Select
Next ctl
Me.Text8.Requery
End Sub


Pls refer to the sample attached
pls post if u have further problems.