Results 1 to 7 of 7
  1. #1
    BGT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    2

    Convert IF statement into Loop Function

    Please can any one help me to change the follwing if statement into loop function or any Function that can summarize my Satement.

    Field of EmpTable :

    If EmpRank = "A1" then
    EmpSalary = 1000 and EmpMealAllawance = 800 and EmpLeaveAllawance = 500
    elseif EmpRank = "A2" then


    EmpSalary = 1050 and EmpMealAllawance = 820 and EmpLeaveAllawance = 580
    .........upto EmpRank "A15".
    The EmpSalary, EmpMealAllawance and EmpLeaveAllawance have a differnces of Constant Value which is 50, 20 and 80 respectively.
    I just summarized the code and i am using MS Access 2016.
    Thanks.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Try
    Code:
    Dim i as Integer, iSalary as Integer, IMeal as Integer, iLeave as Integer
    iSalary = 50
    iMeal = 20
    iLeave = 80
    
    For i = 1 to 15
        If EmpRank = "A"& i then
            EmpSalary = 1000 + (iSalary * (i - 1)
            EmpMealAllowance = 800 + (iMeal * (i -1)
            EmpLeaveAllowance = 500 + (iLeave * (i -1)
            Exit For
        End If
    Next
    Debug.Print "A" & i & " " & EmpSalary & " " &  EmpMealAllowance & " " &     EmpLeavelAllowance
    Comment out the Debug.Print when it is working correctly, as not tested.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I may:
    - on the first iteration won't you just exit the For loop? Maybe it belongs after the End If?
    - each iteration will replace the prior value as nothing is done with the calculation as you go (the debug will only print the last iteration results)?
    - each assignment is missing a closing parenthesis
    - change EmpLeavelAllowance to EmpLeaveAllowance

    The logic is impressive!

    EDIT - on 2nd thought, Exit For probably not needed at all, but if it is, you'd need an Else statement and put it after that.
    Also, EmpRank might have to be incremented. It depends on how this is called or where the values will be coming from. Could also not test and just increment the values using the loop.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Sorry, that is what you get for copying a single line.

    My thoughts were ......
    Once you find the correct A & whatever then you set and exit the loop, else, yes, you just override the values.
    Once you have exited the loop, those values displayed are the ones used.?



    As mentioned not tested as it was written in Notepad.?

    Here is my attempt now I put it into Access

    Code:
    Sub TestALoop(EmpRank As String)
    Dim i As Integer, iSalary As Integer, IMeal As Integer, iLeave As Integer
    Dim EmpSalary As Integer, EmpMealAllowance As Integer, EmpLeaveAllowance As Integer
    iSalary = 50
    IMeal = 20
    iLeave = 80
    
    For i = 1 To 15
        If EmpRank = "A" & i Then
            EmpSalary = 1000 + (iSalary * (i - 1))
            EmpMealAllowance = 800 + (IMeal * (i - 1))
            EmpLeaveAllowance = 500 + (iLeave * (i - 1))
            Exit For
        End If
    Next
    Debug.Print "A" & i & " " & EmpSalary & " " & EmpMealAllowance & " " & EmpLeaveAllowance
    End Sub
    Debug Output
    Code:
    A3 1100 840 660
    A15 1700 1080 1620
    A7 1300 920 980
    At the end of the day, I was trying to show the concept/logic?

    I would probably have used a table, but that opens another can of worms for a new poster.
    Attached Thumbnails Attached Thumbnails Aloop.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yeah, no idea what the outcome is supposed to really look like. With the Exit For, I just don't see a loop happening.
    I'm out until there is clarification on where/what the inputs are and what the output is supposed to be/do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    BGT is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    2
    Quote Originally Posted by BGT View Post
    Please can any one help me to change the follwing if statement into loop function or any Function that can summarize my Satement.

    Field of EmpTable :

    If EmpRank = "A1" then
    EmpSalary = 1000 and EmpMealAllawance = 800 and EmpLeaveAllawance = 500
    elseif EmpRank = "A2" then
    EmpSalary = 1050 and EmpMealAllawance = 820 and EmpLeaveAllawance = 580
    .........upto EmpRank "A15".
    The EmpSalary, EmpMealAllawance and EmpLeaveAllawance have a differnces of Constant Value which is 50, 20 and 80 respectively.
    I just summarized the code and i am using MS Access 2016.
    Thanks.
    Good one.
    I really appreciate the solutions given.
    I will try to implement .
    Once again thank you all.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No need for a loop either, but that is what the O/P requested?

    Code:
    Sub TestALoop2(EmpRank As String)
    
    Dim i As Integer, iSalary As Integer, IMeal As Integer, iLeave As Integer
    Dim EmpSalary As Integer, EmpMealAllowance As Integer, EmpLeaveAllowance As Integer
    iSalary = 50
    IMeal = 20
    iLeave = 80
    
    i = Val(Mid(EmpRank, 2))
    EmpSalary = 1000 + (iSalary * (i - 1))
    EmpMealAllowance = 800 + (IMeal * (i - 1))
    EmpLeaveAllowance = 500 + (iLeave * (i - 1))
    Debug.Print "A" & i & " " & EmpSalary & " " & EmpMealAllowance & " " & EmpLeaveAllowance
    
    End Sub
    Also comes in handy if more that 15 in the future?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 15
    Last Post: 10-17-2018, 09:26 PM
  2. If loop statement
    By Shamli in forum Access
    Replies: 4
    Last Post: 09-14-2018, 03:05 PM
  3. Replies: 4
    Last Post: 10-12-2010, 02:11 PM
  4. For Each LOOP statement
    By zambam737 in forum Programming
    Replies: 3
    Last Post: 10-26-2009, 09:59 PM
  5. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM

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