Results 1 to 2 of 2
  1. #1
    Exadd is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    12

    tax range table change but not picking up when it is calculated

    We recently got change on our tax and this has affected only the range to which 385.00 and less is the non taxible amount.The current table shows this,
    Range CoefficientA CoefficientB
    K7,000.00 0 K0.00
    K18,000.00 0.22 K1,540.00
    K33,000.00 0.3 K2,980.00
    K70,000.00 0.35 K4,630.00
    K250,000.00 0.4 K8,130.00
    K999,999,999.00 0.42 K13,130.00

    Now with the new 1 the table is currently as follows,
    Range CoefficientA CoefficientB
    K9,000.00 0 K0.00
    K23,000.00 0.22 K1,540.00
    K33,000.00 0.3 K2,980.00
    K70,000.00 0.35 K4,350.00
    K250,000.00 0.4 K7,850.00
    K999,999,999.00 0.42 K12,850.00
    However, Im confuced as to what cofficientB represents and most likely its probably why there a no diffrences while tax is calculated.Could someone help me on this one.The new table has been amended according to the new range tax send this month.This is how the module looks like at the moment [
    '*****************************************
    '
    ' Use this module for tax computation only
    '
    '*****************************************
    Option Compare Database
    Option Explicit
    Function Get_Tax(vEmpno, vPayRef, vGross, vDep) As Double
    Dim vTax1, vTax2, vSumTaxSeparate
    Dim sqlst, vAmount, vDays, vLoop, vCtr
    Dim rst As Recordset
    Dim vDailyRate
    Dim ctr, vFNDays

    Dim vMaxSickDays, vRatePerHr
    Dim vMaxDays
    Dim vTotalTax

    vFNDays = DLookup("[TaxFnDays]", "systemsettings")
    ' sum up the tax separated items
    vSumTaxSeparate = DSum("[Amount]", "qryjoballocation", "taxseparate='1' and Payreference='" & vPayRef & "' and [empcode] ='" & vEmpno & "'")
    If IsNull(vSumTaxSeparate) Then
    vSumTaxSeparate = 0
    End If

    ' total gross - separated tax
    vGross = vGross - vSumTaxSeparate

    'computer tax set on regular tax
    vTax1 = Compute_Tax(vGross, vDep)

    'individually compute for tax separated items
    vTax2 = 0
    sqlst = "select * from tblFNJobsAndRates where (taxseparate='1' and Payreference='" & vPayRef & "' and empcode ='" & vEmpno & "');"
    Set rst = CurrentDb.OpenRecordset(sqlst)
    If Not (rst.EOF) Then
    Do While Not rst.EOF
    vAmount = rst!HrsWorked * Val(rst!Rate)
    vDays = rst!Count
    vDailyRate = vAmount / vDays
    ' vTaxArray = 0
    'number of days specified in entry


    'check if sick or leave pay
    'get the total amount for the vl/sl and compute tax on a per day basis
    'get the total number of leave/sick days and base the tax from the total amount
    vMaxDays = 0
    vRatePerHr = 0

    If rst!PayRate = "4" Or rst!PayRate = "3" Then 'vl and sick leave
    If rst!PayRate = "4" Then
    vMaxDays = DLookup("[SickLeaveAllowed]", "tpm_personal", "empcode='" & vEmpno & "'")
    vRatePerHr = Val(rst!Rate)
    ' vRatePerHr = DLookup("[RatePerHour]", "tpm_personal", "empcode='" & vEmpno & "'")
    vTotalTax = Compute_Tax_wDays(vMaxDays, 8 * vRatePerHr, vDep, vFNDays)


    vTax2 = vTax2 + ((vTotalTax / vMaxDays) * vDays)
    Else ' vl leave
    vMaxDays = DLookup("[paidLeaveAllowed]", "tpm_personal", "empcode='" & vEmpno & "'")
    vRatePerHr = Val(rst!Rate)
    'vRatePerHr = DLookup("[RatePerHour]", "tpm_personal", "empcode='" & vEmpno & "'")
    vTotalTax = Compute_Tax_wDays(vMaxDays, 8 * vRatePerHr, vDep, vFNDays)
    vTax2 = vTax2 + ((vTotalTax / vMaxDays) * vDays)
    End If
    Else
    If vDays > vFNDays Then
    vTax2 = vTax2 + Compute_Tax_wDays(vDays, vDailyRate, vDep, vFNDays)
    Else
    vTax2 = vTax2 + Compute_Tax(vAmount, vDep)
    End If
    End If

    rst.MoveNext
    Loop
    End If
    rst.Close

    Get_Tax = vTax1 + vTax2
    End Function
    ' number of days, rate per hour, dependants, 11 - use for fnday
    Function Compute_Tax_wDays(ppDays, ppDailyRate, ppDep, ppFNDays) As Single
    Dim arrayAmount(1 To 26), arrayTax(1 To 6)
    Dim vTaxArray
    Dim ctr, vLoop
    Dim pDays, pDailyRate, pDep, pFNDays
    pDays = ppDays
    pDailyRate = ppDailyRate
    pDep = ppDep
    pFNDays = ppFNDays

    If pDays = 0 Then
    Compute_Tax_wDays = 0
    Exit Function
    End If
    vTaxArray = 0
    vLoop = Int(pDays / pFNDays)
    ctr = 1
    Do While pDays > pFNDays
    arrayAmount(ctr) = pFNDays * pDailyRate
    arrayTax(ctr) = Compute_Tax(arrayAmount(ctr), pDep)
    vTaxArray = vTaxArray + arrayTax(ctr)
    pDays = pDays - pFNDays
    ctr = ctr + 1
    Loop
    arrayAmount(ctr) = pDays * pDailyRate
    arrayTax(ctr) = Compute_Tax(arrayAmount(ctr), pDep)
    vTaxArray = vTaxArray + arrayTax(ctr)

    Compute_Tax_wDays = vTaxArray
    End Function

    Function Compute_Tax(pGross, pDependants) As Single
    Dim N, sqlst, coA, coB, GrossTax
    Dim ttax As Recordset
    Dim DepRebate, NetTax
    Dim tens, ones
    If IsNull(pDependants) Or pDependants = "" Then
    pDependants = 0
    End If
    If pDependants > 3 Then
    pDependants = 3
    End If
    If IsNull(pGross) Then
    pGross = 0
    End If


    ' this routine will accurately compute the tax to the nearest toea
    ' the amount should always be an odd number to get a correct tax
    ' only applicable for amounts less than 800
    If pGross < 800 Then
    tens = Int(pGross)
    ones = pGross - tens
    If ones > 0 Then
    tens = tens + 1
    End If
    If (tens Mod 2) = 0 Then
    tens = tens + 1
    End If
    pGross = tens
    End If
    'step 1. calculate income N
    '=============================
    N = (pGross * 26) - 200

    'step 2. calculate gross tax
    '===========================
    sqlst = "select * from tblTaxResidentRates where range >= " & N

    Set ttax = CurrentDb.OpenRecordset(sqlst, dbOpenSnapshot)
    ttax.MoveFirst
    coA = ttax!CoefficientA
    coB = ttax!CoefficientB
    ttax.Close
    GrossTax = (N * coA) - coB

    'step 3. calculate dependant rebate
    ' values are hard coded because the formula also changes when values are changed
    '==================================
    If pDependants = 0 Then
    DepRebate = 0
    Else
    If N >= 0 And N <= 7800 Then
    DepRebate = 45 + (30 * (pDependants - 1))
    ElseIf N >= 7800.01 And N <= 18500 Then
    DepRebate = GrossTax * (0.15 + (0.1 * (pDependants - 1)))
    ElseIf N >= 18500.01 Then
    DepRebate = 450 + (300 * (pDependants - 1))
    End If
    End If
    NetTax = GrossTax - DepRebate

    If NetTax < 0 Then
    NetTax = 0
    End If

    'step 4. calculate fortnight tax
    '===============================
    Compute_Tax = Format(NetTax / 26, "#0.00")

    End Function

    ] I only have limited time to do this and I have been working night and day to figuring this out.Kindly assist as I badly need help and my time is running out.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    If you don't know, or can't find out from the organization, what coefficientA and coefficientB represent, then readers can only guess.
    I do not see anything in the post that tells about the
    only the range to which 385.00 and less is the non taxible amount.
    What you are showing, in my view, is very specific to the organization involved. You will need someone from the org who is familiar with the underlying system/business to identify what and where the change should be applied.

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

Similar Threads

  1. Query Date Range Based on Calculated Fields
    By wilsoa in forum Queries
    Replies: 6
    Last Post: 03-08-2012, 02:41 PM
  2. Queries only Picking up some information
    By Lois in forum Queries
    Replies: 5
    Last Post: 11-22-2011, 05:08 AM
  3. Picking up Date on Import
    By DonL in forum Import/Export Data
    Replies: 4
    Last Post: 08-10-2011, 07:06 AM
  4. Me not picking up new columns in table
    By asterismw in forum Programming
    Replies: 3
    Last Post: 03-11-2011, 02:00 PM
  5. Replies: 1
    Last Post: 05-25-2010, 02:58 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