Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,938
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    414
    Quote Originally Posted by jbeets View Post
    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.

  3. #18
    jbeets is offline Advanced Beginner
    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. #19
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    414
    Quote Originally Posted by jbeets View Post
    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

  5. #20
    jbeets is offline Advanced Beginner
    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. #21
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    414
    Quote Originally Posted by jbeets View Post
    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 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating a score based on yes/no parameters
    By okieheart in forum Queries
    Replies: 5
    Last Post: 10-16-2018, 07:46 AM
  2. Replies: 4
    Last Post: 11-02-2017, 09:58 AM
  3. Calculating Percent of a Test Score
    By cohnhead in forum Queries
    Replies: 5
    Last Post: 11-21-2014, 03:22 PM
  4. Replies: 1
    Last Post: 06-24-2013, 02:24 AM
  5. Update Multiple Fields based on Selection (inTable)
    By aguestnga in forum Database Design
    Replies: 3
    Last Post: 11-03-2011, 04:17 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums