Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Updating a field in ACCESS via VBA based on a range of values in ano.field same table

    Hi,
    My post's title would have been clear.

    I have been trying to use a variety of method (nothing seems to work). The question is how do I update a field with four vaues TIER1, TIER2, TIER3, TIER4 under the CurrentTIER field based on the following ranges of values in the Balance column of the same table. the range of values are

    0-300 -> TIER1
    300 - 750 ->TIER2
    750 - 1500 -> TIER3
    >1500 ->TIER4

    I tried to use Case did not work. This I will have to accomplish by writing a code in VBA connecting to a ACCESS Database on a table called TIER.

    If someone can give me a sample code, it will be very much appreciated. Thanks

    It could be possihle that someone would have discussed this but I am new to the post and even if you could direct me to a thread which has the solution I would be happy. Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The conditions of evaluation you propose are irrational:
    0-300 -> TIER1
    300 - 750 ->TIER2
    750 - 1500 -> TIER3
    >1500 ->TIER4
    If we're dealing with integer values it should be: 0 to 299 --> TIER1, 300 to 749 --> TIER2, etc. OR 0 to 300 --> TIER1, 301 To..., etc.

    You could use four queries, like this:
    Code:
    UPDATE TIER SET TIER.CurrentTIER = 'TIER1' WHERE TIER.Balance Between 0 And 299;
    UPDATE TIER SET TIER.CurrentTIER = 'TIER2' WHERE TIER.Balance Between 300 And 749;
    UPDATE TIER SET TIER.CurrentTIER = 'TIER3' WHERE TIER.Balance Between 750 And 1499;
    UPDATE TIER SET TIER.CurrentTIER = 'TIER4' WHERE TIER.Balance > 1499;
    Used in a VBA procedure:
    Code:
    Function UpdateTier_SQL()
    
        Const c_Tier1 As String = "UPDATE TIER SET TIER.CurrentTIER = 'TIER1' WHERE TIER.Balance Between 0 And 299;"
        Const c_Tier2 As String = "UPDATE TIER SET TIER.CurrentTIER = 'TIER2' WHERE TIER.Balance Between 300 And 749;"
        Const c_Tier3 As String = "UPDATE TIER SET TIER.CurrentTIER = 'TIER3' WHERE TIER.Balance Between 750 And 1499;"
        Const c_Tier4 As String = "UPDATE TIER SET TIER.CurrentTIER = 'TIER4' WHERE TIER.Balance > 1499;"
        
        CurrentDb.Execute c_Tier1
        CurrentDb.Execute c_Tier2
        CurrentDb.Execute c_Tier3
        CurrentDb.Execute c_Tier4
    
    End Function
    If for any reason you prefer to loop through a recordset:
    Code:
    Function UpdateTier_Rst()
    
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("TIER", dbOpenDynaset)
        With rst
            Do Until .EOF
                .Edit
                Select Case !Balance
                    Case 0 To 299:      !CurrentTIER = "TIER1"
                    Case 300 To 749:    !CurrentTIER = "TIER2"
                    Case 750 To 1499:   !CurrentTIER = "TIER3"
                    Case Is > 1499:     !CurrentTIER = "TIER4"
                End Select
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    5

    Thank you so much

    It worked and I really appreciate your provding multiple solutions. thanks again

    Satish

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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