# Calculating a score based on cbo selection while looking at multiple fields

1. 1. Correct

2. Why would there be div by 0 error - expression is dividing by 6? If condition is not True it will be treated as False, even if a triple-state checkbox is Null (which can only be with an UNBOUND checkbox). So for any checkbox not checked, it is False and the alternate value will apply. Isn't that what you want?

2. Originally Posted by jbeets
accesstos - Thank you for providing some help! So a couple questions...
1) Can you help me understand the formula you wrote that accounts for the variance field? Is it saying - If checkbox1 (the Yes response) is checked then count or add 1 otherwise subtract the variance field then divide the total by 6?
You are welcome!

As I said, I didn’t understand the use of the variance but I tried to apply this:
If No is chosen currently the user gets a 0 on that question. Instead of the 0 I need it to use the % that will be entered in the variance text box.
So the statement IIf([chk1];[chk1];-0,05) returns -1 in "Yes" case (Checked) and -0.05 in any other case.
The whole expression returns the positive sum of the statements divided by 6 (not 0), so, if all answers are "No", the score is 0.15/6 (3%), but in case of all "Yes", the score will be 6/6 (100%).

If you want to add the variance only in case of total 0 score, you have to use an IIF([txtAnswers]=0;[txtVariance];[txtAnswers]) where [txtAnswers] a textbox with the score expression.

I hope it helps.

Windows 10 Access 2013 64bit
Join Date
Feb 2020
Posts
92
Ok - so my questions all have a Yes, No or N/A option. But it sounds like i will only have the Yes/No or True/False options available. (I'm sorry for my ignorance, I havent yet done much with the checkbox options) Is this a viable option for my need?

4. Originally Posted by jbeets
Ok - so my questions all have a Yes, No or N/A option.
Hmm…That "N/A" option ruins the "Checkbox" plan.

So, rolling back, the VBA code for the score could be as seems below:

Code:
```Private Sub btnDocScore_Click()
Dim c As Control
Dim intYes As Integer
Dim n As Integer

Me!DocScore = Null
For Each c In Me.Controls
If c.ControlType = acComboBox And c.Tag = "Doc" Then
If IsNull(c) Then
MsgBox ("ComboBox selection left blank. Please ensure all drop downs are selected before continuing.")
c.SetFocus
Exit Sub
Else
If c = "Yes" Then
intYes = intYes + 1       'Count only "Yes"
End If
n = n + 1                     'Count the total questions
End If
End If
Next c

With Me!DocScore
'Calculate score
.Value = intYes / n                   'Is better to set the DocScore's Format to 0% in design view
If Me!cboDocQ2 = "No" Then
.Value = .Value + Me!txtDQ2Var    'Add the variance to the total score
End If
End With

txtDocStatus = "Complete"
'========================================================
'About controls BackColor, better use conditional format
'========================================================
End Sub```
Hope this helps.

Cheers,
John

Windows 10 Access 2013 64bit
Join Date
Feb 2020
Posts
92
Thank you so much! This is exactly what I needed. The suggestions and changes to the code I was already working with are fantastic and you solved my problem! Thank you for your help!

6. Originally Posted by jbeets
Thank you so much! This is exactly what I needed. The suggestions and changes to the code I was already working with are fantastic and you solved my problem! Thank you for your help!
You are very welcome!

The blue additions of code will make the procedure more robust:
Code:
```    If n > 0 Then
With Me!DocScore
'Calculate score
.Value = intYes / n                          'Is better to set the DocScore's Format to "0%" in design view
If Me!cboDocQ2 = "No" Then
.Value = .Value + Nz(Me!txtDQ2Var, 0)    'Add the variance to the total score
End If
End With
End If```
Good luck with your project and - thanks for the star!

Page 2 of 2 First 12