Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    9

    Unanswered: Help select case programming in access

    I am trying to use the select/case programming structure in visual basic with in Microsoft access to reference a range of numbers and depending on that range populate a text box with the corresponding text. It works great for any numbers that are positive. However when I attempt to get the same results with the negative values, visual basic is not recognizing it. Is there a way to get visual basic to recognize the negative values? I will post my code here. Thanks for any help.



    Private Sub Variance_AfterUpdate()
    On Error Resume Next
    Select Case Variance.Value
    Case 0.01 To 4.99
    Tier.Value = "Under Tier"
    Case -0.01 To -4.99
    Tier.Value = "Under Tier"
    Case 5 To 19.99
    Tier.Value = "Tier 4"
    Case -5 To -19.99
    Tier.Value = "Tier 4"
    Case -20 To -49.99
    Tier.Value = "Tier 3"
    Case 20 To 49.99
    Tier.Value = "Tier 3"
    Case 50 To 99.99
    Tier.Value = "Tier 2"
    Case Is < 0, -50 To -99.99
    Tier.Value = "Tier 2"
    Case Is < 0, -100 To -10000
    Tier.Value = "Tier 1"
    Case 100 To 100000
    Tier.Value = "Tier 1"
    Case 0
    Tier.Value = "No Variance"
    End Select
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Visual Basic does recognize negative values. However, you have contradictory or redundant evaluation expressions in the cases: If you have the expression
    Code:
    Case Is < 0
    it makes no sense to use expression such as
    Code:
    Case -0.01 To -4.99
    in the same SELECT CASE BLOCK.
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    9
    If I use case < 0, it will only return the first statement that I have coded that for. I need the positive and their negative counter parts to return the same result. For example, i need 50 to 99.99 and -50 to -99.99, give me tier 2. If i put case <0, where
    case is <0, -0.01 to -4.99
    tier.value ="under Tier_
    case is <0, -50 to -99.99
    tier.value="Tier 2"

    It will only display under tier, regardless if the value range falls between -50.99 and -99.99.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Step through the code, looking at the values in the variables. This will give you a better idea of what is going wrong, and where.

    Alternatively, use the Abs() function to return the absolute value of Variance, and just plus that into a SELECT CASE statement without worrying about the negative values.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    on error is, or at least should be superfluous in a select case statement, thats what the 'case else' statement is for
    using in error resume next willy nilly is a recipe for disaster.
    also please indent your code, and when posting code here wrap it in the [ c o d e ] and [ / c o d e ] tages, but remove the spaces so [ c o d e ] becomes [code]

    Code:
    Select Case abs(Variance.Value)
      Case 0
        Tier.Value = "No Variance"
      Case 0.01 To 4.99
        Tier.Value = "Under Tier"
      Case 5 To 19.99
        Tier.Value = "Tier 4"
      Case 20 To 49.99
        Tier.Value = "Tier 3"
      Case 50 To 99.99
        Tier.Value = "Tier 2"
      Case 100 To 100000
        Tier.Value = "Tier 1"
      case else
        Tier.Value = "unexpected variance of:" & Variance.Value
    End Select
    not intending to be preachy but I'd also suggest you build your case statements in ascending order, the reason being its logical and makes it easier for others to read your code
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2013
    Posts
    9
    Thanks for all the help and advice

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You have to always be aware of the order that you're using to build your Select Case construct, especially when the Case at hand is Numeric, because once a given Case evaluates as True, Access fires the code under that Case and exits the construct. In other words, given

    Code:
    Select Case Me.TargetTextBox
      
      Case Is > 1
       MsgBox "Over 1"
       
      Case Is > 2
       MsgBox "Over 2"
      
      Case Is = 3
       MsgBox "Equals 3"
      
      Case Else
       MsgBox "Else"
    
    End Select
    If

    Me.TargetTextBox = 3

    instead of the Messagebox saying

    "Equals 3"

    it is going to say

    "Over 1"

    and then exit the Select Case construct, even though

    Me.TargetTextBox = 3

    is True!

    That's because in the order used above, the first Case that evaluates as True is

    Case Is > 1

    So, in this case, you'd need to use
    Code:
    Select Case Me.TargetTextBox
      
      Case Is = 3
       MsgBox "Equals 3"
       
      Case Is > 2
       MsgBox "Over 2"
      
      Case Is > 1
       MsgBox "Over 1"
      
      Case Else
       MsgBox "Else"
    
    End Select


    Linq ;0)>
    Last edited by Missinglinq; 04-25-13 at 17:28.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •