Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Exclamation Unanswered: problem in if statement

    i am working on a tax program. i write the following code in the OnUpdate Event of a text field.

    Private Sub Combo232_AfterUpdate()
    If ([sex] = "MALE" And [total_income] > 110000 And [total_income] <= 150000) Then
    Me.taxOnIncome = (([total_income] - 110000) * 10 / 100)
    Else
    Me.taxOnIncome = 0
    End If
    If ([sex] = "MALE" And [total_income] > 150000 And [total_income] <= 250000) Then
    Me.taxOnIncome = (([total_income] - 150000) * 20 / 100) + 4000
    Else
    If ([sex] = "MALE" And [total_income] > 250000) Then
    Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 24000
    End If
    End If

    If ([sex] = "FEMALE" And [total_income] > 145000 And [total_income] <= 150000) Then
    Me.taxOnIncome = (([total_income] - 145000) * 10 / 100)
    Else
    Me.taxOnIncome = 0
    End If
    If ([sex] = "FEMALE" And [total_income] > 150000 And [total_income] <= 250000) Then
    Me.taxOnIncome = (([total_income] - 150000) * 20 / 100) + 500
    Else
    If ([sex] = "FEMALE" And [total_income] > 250000) Then
    Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 20500
    End If
    End If

    If ([sex] = "SR. CITIZEN" And [total_income] > 195000 And [total_income] <= 250000) Then
    Me.taxOnIncome = (([total_income] - 195000) * 20 / 100)
    Else
    Me.taxOnIncome = 0
    End If
    If ([sex] = "SR. CITIZEN" And [total_income] > 250000) Then
    Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 11000
    End If

    my problem is that after completing this prog, it calculates the result only for the last part i.e. Sr. Citizen. but when i remove the "Sr. Citezen" part then it gives the results only for "Women " part. But when i remove both "women" and "Sr. citezen" part then it gives results for "Male" part.

    I want all these 3 fields working. Please help me.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Take out all the Else clauses that Me.taxOnIncome = 0. Put that statement up at the top if you want to have it default.

    Also, a better way to do this would be construct a table called, maybe, LookupTable:

    Code:
    Sex     IncomeBracketLow  IncomeBracketHigh    TOISubtract  TOITimes TOIAdd
    Male    110,000                150,000           110000        10           0
    ...
    So the entries in the table just correspond to your IF THEN statements.

    Then do this:

    Code:
    criteria = "Sex = '" & [Sex] & "' AND IncomeBracketLow < " & totalIncome & " AND (IncomeBracketHigh Is Null OR IncomeBracketHigh >= " & totalIncome & ")"
    If DBCount("*", "LookupTable", criteria) = 1 Then
       me.taxOnIncome = (([total_income] - DBLookup("TOISubtract", "LookupTable", criteria)) * 
          DBLookup("TOITimes", "LookupTable", criteria) / 100) + DBLookup("TOIAdd", "LookupTable", criteria)
    Else
       me.taxOnIncome = 0
    End If
    That way it's a little bit easier to update entries.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Lose the if, and change it to a select case. as in

    Select Case sex

    Case "MALE"

    If ([total_income] > 110000) And ([total_income] <= 150000) Then
    Me.taxonincome = (([total_income] - 110000) * 0.1)
    ElseIf ([total_income] > 150000) And ([total_income] <= 250000) Then
    Me.taxonincome = (([total_income] - 150000) * 0.2) + 4000
    etc.

    Hope this helps,
    Sam

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Thumbs up

    You could simplify Sam's response

    Select Case sex

    Case "MALE"

    IF[total_income] > 250000) Then
    Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 24000
    else
    if [total_income] > 150000 then
    Me.taxOnIncome = (([total_income] - 150000) * 20 / 100) + 4000
    else
    if [total_income] > 110000 then
    Me.taxOnIncome = (([total_income] - 110000) * 10 / 100)
    else
    Me.taxOnIncome = 0
    end if
    end if
    end if

    etc.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Poppa Smurf
    You could simplify Sam's response

    Select Case sex

    Case "MALE"

    IF[total_income] > 250000) Then
    Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 24000
    else
    if [total_income] > 150000 then
    Me.taxOnIncome = (([total_income] - 150000) * 20 / 100) + 4000
    else
    if [total_income] > 110000 then
    Me.taxOnIncome = (([total_income] - 110000) * 10 / 100)
    else
    Me.taxOnIncome = 0
    end if
    end if
    end if

    etc.
    aside from the opinion that this would be best handled as rules within a table, rather than hardcoding I think you would be better off doing something like
    select case [total_income]
    case is > 250000
    if gender=male then
    else
    endif
    case is > 150000 and <=250000

    and so on.

    I think it would make smarter sense to pull the tax rate from a table based on income using SQL.. its easier maintained, changes in rates don't require a rewrite (they will always require re-testing) but shouldn't require someone at short notice to change code which is always a dangerous practise.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by Poppa Smurf
    You could simplify Sam's response

    Select Case sex

    Case "MALE"

    IF[total_income] > 250000) Then
    Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 24000
    else
    if [total_income] > 150000 then
    Me.taxOnIncome = (([total_income] - 150000) * 20 / 100) + 4000
    else
    if [total_income] > 110000 then
    Me.taxOnIncome = (([total_income] - 110000) * 10 / 100)
    else
    Me.taxOnIncome = 0
    end if
    end if
    end if

    etc.
    Erm... that's complicating it. Why use multiple nested if statements, when one single if statement?

    Code:
    Select Case sex
       Case "MALE"
          IF[total_income] > 250000) Then
             Me.taxOnIncome = (([total_income] - 250000) * 30 / 100) + 24000
          ElseIf [total_income] > 150000 Then
             Me.taxOnIncome = (([total_income] - 150000) * 20 / 100) + 4000
          ElseIf [total_income] > 110000 Then
             Me.taxOnIncome = (([total_income] - 110000) * 10 / 100)
          Else
             Me.taxOnIncome = 0
          End If
       Case "FEMALE"
          Similar IF statement...
    End Select
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    Thank you friends. With your help my problem is solved. thanks a lot.

Posting Permissions

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