calculated field

[edreamers]

i am working on a tax calculation program. I used many calculated fields on the form. But on the final field i dont have an idea how and where to write the code. Actually i want to use multi if statement in the manner below:-

if sex=Male
and income>110000 and income<=150000
then tax=(income-110000) *10

elseif if sex=male
and income>150000 and income<=250000
then tax=(income-150000)*20+4000

elseif sex = male
and income>250000
then tax(income-250000)*30+24000

Please tell what will be the proper syntax for this problem in access and where to write this code. in other fields i write code in control source fields. Please help me, Thanks in advance.

 

[georgedwilkinson]

Put the rates in a table (that what Access is for) and get the value by querying the table.

 

[boblarson]

I would create a function in a standard module to do the work of calculation and then instead of using a bunch of IF statements, use Select Case statements like:

Dim curTaxRate As Currency
Dim curAdditional As Currency
Dim curAdjustment As Currency

Select Case Me![Sex]

Case "male"

   Select Case Me![Income]

   Case > 110000 And <= 150000
      curTaxRate = 10
      curAdjustment = 110000

   Case > 150000 And <=250000
      curTaxRate = 20
      curAdditional = 4000   
      curAdjustment = 150000

   Case > 250000
      curTaxRate = 30
      curAdditional = 24000
      curAdjustment = 250000

   End Select
Case "female"
' ... similar stuff here
End Select

Me![Tax]=((Me![Income]-curAdjustment)*curTaxRate) + curAdditional

 

[boblarson]

Actually, George is right - put the values that I set as variables in a table and pull them using DLookup at the time you need them. Then you can change them at any time without having to rewrite the program. My bad ...

 

[edreamers]

Thanks for the help brother.

Actually I am not a programmer by any means (mostly a network admin by trade), so I have no real skills in VB.

Should I write this code in New Module.

 

[georgedwilkinson]

No need for a module or programming experience. Use a table and use the visual query builder to get your data.

 

[gemma-the-husky]

edreamers

seriously, be very careful about payroll programmes - if this is just for an approximation then no problems- if this is intended to be for serious use its really really hard - its definitely the last thing i would try and do myself.

if you are expert then no problem, and i apologise for the folowing

----
some issues with tax are

tax codes/week 1 basis/taxfreepay calcs/emergency codes/maximum deductions/taxable/non taxable pay elements/leavers/new starters/p35/p45/p60/p11d etc etc it goes on

then all the same dfiiculties with NI, and other deductions

--------
if you are new to access, this will be even harder

eg even starting with text descriptions for "male"/"female" is not ideal - for optimum efficiency these ought to be numeric lookups

 

[edreamers]

Now i am facing a new problem. My calculated fields are not saving its results in table fields. (e.g. textbox1= textbox2+textbox3) Although they show result in form but not save in table. And my tax calculation depends heavily on these fields. How could I use the result of calculated fields for other calculation. Thanks in advance