Ms Maths Functions - Microsoft Community

below function tested in immediate window?

 

1. function salary()
2. dim taxamount currency
3. if 900 >= 800 then
4. taxamount = 800 * 0.25
5. end if
6. if 3000 >= 2100 then
7. taxamount = taxamount + 2100 * 0.3
8. end if
9.  if 6000 >= 5000 then
10. taxamount = taxamount + 5000 * 0.375
11.  end if
12. salary = taxamount
13. end function

 

kindly note values represent actual control on report , how call function in control called txtfinaltax , gives me needed figure of 2705.

 

regards

 

chris

function makes no sense return 2705.  i'm guessing either trying compute net salary value on basis of gross salary value, tax-free allowance 800 currency units, in case suitable function be:

function netsalary(grosssalary currency) currency

    dim taxamount currency
    dim taxableamount currency
    
    if grosssalary <= 800 then
        netsalary = grosssalary
    else
        taxableamount = grosssalary - 800
        select case taxableamount
            case < 2100
            taxamount = taxableamount * 0.25
            case < 5000
            taxamount = taxableamount * 0.3
            case else
            taxamount = grosssalary * 0.375
        end select
        
        netsalary = grosssalary - taxamount
    end if
    
end function

or trying return amount of tax payable on gross salary amount, in case suitable function be:

function taxdue(grosssalary currency) currency

    dim taxamount currency
    dim taxableamount currency
    
    if grosssalary <= 800 then
        taxdue = 0
    else
        taxableamount = grosssalary - 800
        select case taxableamount
            case < 2100
            taxamount = taxableamount * 0.25
            case < 5000
            taxamount = taxableamount * 0.3
            case else
            taxamount = grosssalary * 0.375
        end select
        
        taxdue = taxamount
    end if
    
end function

in either case you'd call function controlsource property of text box in report, passing in employee's gross salary argument.

however, above functions encode data in code, i.e. tax free allowance, start of each tax band, , differential rates each tax band.  bad practice.  fundamental principle of database relational model information principle (codd's rule #1). requires data stored values @ column positions in rows in tables, , in no other way.

current tax free allowance, start of each tax band, , differential rates each tax band should values in referenced tables.  function not able use select case construct of course, need determine tax band applied on basis of taxable salary (gross salary less tax-free allowance) falling within minimum , maximum values each band.  basis maximum taxable amount below taxable salary in current record.

Office / Access / Microsoft Office Programming / Office 2016