Results 1 to 6 of 6
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    Select Case statement not consistently giving a correct result


    Hello - I have a data entry form where the user inputs results from a quality audit. There are several combo boxes in which they can choose "Yes" or "No". They also fill in text boxes "Over" and "Under" with a dollar amount.
    The below VBA calculates the number of "No" responses in the comboboxes, putting the results in a textbox "txtfails".
    There is another textbox "txtPercentDeviation" where a percentage is calculated showing the dollar variance between the estimates.

    The Select Case statement then looks at each box and provides a score. The total score is calculated by adding the scores from the two boxes and then dividing that by 2.

    For example: if the percent deviation is less than 5% the score would equal "3" and 2 fails would receive a score of "2". The math then looks like: (3+2)/2 = 2.5

    The issue I am having is that it is not consistent. Sometimes it comes out to "2.5" and other times it will be "2" even though the same values were entered in the form. Seems to me that the math is the math... so the only other option must be that I have something not written correctly in the code. I really appreciate any help you can provide!
    Code:
    Private Sub CalculateScore_Click()
    Dim target_control As Control
    Dim control_Value As Integer
    Dim control_Count As Integer
    Dim UserID
    UserID = Environ("UserName")
     Me.txtOver = Me.DimenstionsOver + Me.QuantityOver + Me.RepairReplaceOver + Me.PricingOver + Me.Overhead_ProfitOver + Me.LumpSumOver + Me.ConsiderationsOver + Me.DepreciationOver
     Me.txtUnder = Me.DimenstionsUnder + Me.QuantityUnder + Me.RepairReplaceUnder + Me.PricingUnder + Me.Overhead_ProfitUnder + Me.LumpSumUnder + Me.ConsiderationsUnder + Me.DepreciationUnder
     
        'If IsNull(Me.txtUserID.Value) = False Then
    'do nothing
    'Else
        'If IsNull(Me.txtUserID.Value) = True Then
    'Me.txtUserID = UserID
    'End If
    'End If
    If IsNull(Me.txtDateReInspected.Value) = False Then
    'do nothing
    Else
        If IsNull(Me.txtDateReInspected.Value) = True Then
    Me.txtDateReInspected.Value = Date
    
    End If
    End If
    control_Value = 0
    control_Count = 0
    For Each target_control In Me.Controls
        Select Case target_control.Name
            Case "cboPhysicalReview", "cboReviewOf"
            Case Else
        If TypeName(target_control) = "ComboBox" Then
            If Not target_control.Value = "Yes" Then
        
                control_Value = control_Value + 1
                control_Count = control_Count + 1
            End If
        End If
    End Select
    Next
    txtFails = control_Value
    Select Case Me.txtPercentDeviation.Value
        Case 0 To 0.05
            Me.txtDeviationScore.Value = 3
        Case 0.06 To 0.15
            Me.txtDeviationScore.Value = 2
        Case 0.16 To 0.49
            Me.txtDeviationScore.Value = 1
        Case Else
            Me.txtDeviationScore.Value = 0
    End Select
    Select Case Me.txtFails.Value
        Case Is <= 1
            Me.txtDifferenceScore.Value = 3
        Case 2 To 3
            Me.txtDifferenceScore.Value = 2
        Case 4 To 5
            Me.txtDifferenceScore.Value = 1
        Case Else
            Me.txtDifferenceScore.Value = 0
    End Select
    Score = (txtDeviationScore + txtDifferenceScore) / 2
    For Each target_control In Me.Controls
        If TypeName(c) = "ComboBox" Then
            If target_control.Value = "" Then na = MsgBox("All dropdown boxes must be filled out before continuing. Please choose Yes, No, Partially or N/A before continuing.", vbOKOnly, "Quarterly Audits")
        End If
    Next target_control
    End Sub

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of the database with enough records to highlight the problem?

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would also point out that with

    Code:
    Case 0 To 0.05
            Me.txtDeviationScore.Value = 3
        Case 0.06 To 0.15
            Me.txtDeviationScore.Value = 2
    a value of 0.055 will not be handled as it is greater than 0.05 and less than 0.06 - so it will default to 0 under case else

    you might need to round the value to 2dp e.g.

    Select Case round(txtPercentDeviation,2)

    note you don't nee me. or .value as both are the defaults

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    don't use text boxes to do the math. I've seen conditions where 1<>1.
    put the value in a variable with the same data type

    dim nVar1 as single, nVar2 as single.

    nVar1 = round(txtPercentDeviation,2)
    select case nVar1

    it may help.

  5. #5
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    That makes complete sense! Thank you so much for the advice!

  6. #6
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Ok - I think I made the changes that you suggested but sadly I am still getting inconsistent results. Here is the updated code - any other suggestions on whatt I need to change to make this work?

    For example: I have one with a Percentage variance of 6% (should score 2) and 5 fails (score of 1) = (2+1)/2 = 1.5 but instead I get 2.5

    Code:
    Private Sub CalculateScore_Click()
    Dim target_control As Control
    Dim control_Value As Integer
    Dim control_Count As Integer
    Dim UserID
    Dim nVar As Single, nVar2 As Single
    nVar1 = Round(txtPercentDeviation, 2)
    UserID = Environ("UserName")
     Me.txtOver = DimenstionsOver + QuantityOver + RepairReplaceOver + PricingOver + Overhead_ProfitOver + LumpSumOver + ConsiderationsOver + DepreciationOver
     Me.txtUnder = DimenstionsUnder + QuantityUnder + RepairReplaceUnder + PricingUnder + Overhead_ProfitUnder + LumpSumUnder + ConsiderationsUnder + DepreciationUnder
     
    If IsNull(Me.txtDateReInspected.Value) = False Then
    'do nothing
    Else
        If IsNull(Me.txtDateReInspected.Value) = True Then
    Me.txtDateReInspected.Value = Date
    
    End If
    End If
    control_Value = 0
    control_Count = 0
    For Each target_control In Me.Controls
        Select Case target_control.Name
            Case "cboPhysicalReview", "cboReviewOf"
            Case Else
        If TypeName(target_control) = "ComboBox" Then
            If Not target_control.Value = "Yes" Then
        
                control_Value = control_Value + 1
                control_Count = control_Count + 1
            End If
        End If
    End Select
    Next
    txtFails = control_Value
    Select Case nVar1
        Case 0 To 0.05
            txtDeviationScore = 3
        Case 0.06 To 0.15
            txtDeviationScore = 2
        Case 0.16 To 0.49
            txtDeviationScore = 1
        Case Else
            txtDeviationScore = 0
    End Select
    Select Case nVar2
        Case Is <= 1
            txtDifferenceScore = 3
        Case 2 To 3
            txtDifferenceScore = 2
        Case 4 To 5
            txtDifferenceScore = 1
        Case Else
            txtDifferenceScore = 0
    End Select
    Score = (txtDeviationScore + txtDifferenceScore) / 2
    For Each target_control In Me.Controls
        If TypeName(c) = "ComboBox" Then
            If target_control.Value = "" Then na = MsgBox("All dropdown boxes must be filled out before continuing. Please choose Yes, No, Partially or N/A before continuing.", vbOKOnly, "Quarterly Audits")
        End If
    Next target_control
    End Sub
    Thank you so much for helping!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error In Select Case Statement
    By archie1_za in forum Programming
    Replies: 11
    Last Post: 12-05-2017, 03:26 AM
  2. Replies: 1
    Last Post: 03-06-2015, 11:16 AM
  3. Should I /can I use a Select Case statement?
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 12-13-2014, 12:08 PM
  4. Replies: 4
    Last Post: 10-09-2014, 12:58 PM
  5. Help with Select Case statement
    By focosi in forum Access
    Replies: 4
    Last Post: 08-09-2011, 12:01 AM

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