Display only positive values in my query

[Takstein]

Hi.. I am a beginner using access and there is probably an easy solution for this which i dont know about!

I have made a query where i calculate the tax deductions for a payroll system. my problem is that different workers are to be taxed differently given maritial status and number of kids as well as net income.

I have managed to calculate everything with the kids and wifes but now i need to sort the table after how much they earn to deduct taxes. I have a "first stage NetPay field" now, where i can see the Gross pay minus allowances for wifes and kids.

Now, if that value(first stage netpay) is under 8840 no more deductions are to occur. If the value is between 8840 and 10000 i have to deduct 15% of the excess above 8840

If the value is between 10000 and 20000 i have to deduct a further 174+ 25% of excess over 10000

If the value is above 20000 i have to deduct 2674+ 31% of excess over 20000


So far what i have done is make a new coloumn, where i take "first stage netpay field" and subtract 8840. If the value is negative i thought i could show only the positive values by enterin >0 in the criteria field for the query, this however will make no posts show up..

(I tried entering Abs() in the Field coloumn where i made the expression, and this worked. )


So my question is: is there a function like Abs() i could use that would convert negative values into 0?

Or do any of you guys have a better idea of how to solve this?




Hope someone can help me...


Takstein

 

[Moniker]

Can you post a sample of that (without real data in it that can be traced to anyone/any place, but that has negative numbers)? This has to be very simple, but there are a few potential problems and a lot of potential solutions.

FYI, Abs() is a conversion function that will convert any negative to a positive (its ABSolute value, so to speak). That is not the correct way to handle that.

 

[Takstein]

Can you post a sample of that (without real data in it that can be traced to anyone/any place, but that has negative numbers)? This has to be very simple, but there are a few potential problems and a lot of potential solutions.

FYI, Abs() is a conversion function that will convert any negative to a positive (its ABSolute value, so to speak). That is not the correct way to handle that.

I know that Abs() converts it to absolute values. What i meant with that was that if i wrote Abs("my expression") i got all positive values. But if i try to write anything in the qriteria sectoin on the form it wont show any posts and ill get asked to enter inputs...??

So thats why i was thinking that if there was a function a bit like Abs() that could change negative values to 0, this would work...


I'v added a few photos, maybe thatll help!

 

[Moniker]

Here's the solution for the criteria2 picture. Change the formula for PayeStage to:

PayeStage: =Switch([NetPay-8840]>=0,[NetPay-8840],True,0)

Thinking about the other, although it's tough to get a feel for it. Give me a bit as basketball is on.

 

[Takstein]

Here's the solution for the criteria2 picture. Change the formula for PayeStage to:

PayeStage: =Switch([NetPay-8840]>=0,[NetPay-8840],True,0)

Thinking about the other, although it's tough to get a feel for it. Give me a bit as basketball is on.

I tried entering this, but i only get errors.. It says that my expression has an invalid syntax. "You wrote for an example in an operand without an operator" (translated from my norwegian version access). I read up on the function Switch, and tried to enter it the same way as the help file, but still i got the same expression)

Do you think m gettin troubles since i have a norwegian access version?

Thanks for your help so far, appreciate it alot! This thing has been buggin me for two days straight now...


Cheers,

Arni

 

[Moniker]

OMG, I was too hasty. Here that is corrected:

PayeStage: =Switch([NetPay1]-8840>=0,[NetPay1]-8840,True,0)

Apologies for the confusion.

 

[Takstein]

I just got it too! But it seems in my norwegian version i have to use ; instead of ,...

Anyways.. Thank you so much for your help!

man this is a good load off my chest...



Cheers,


Arni

 

[Moniker]

Glad it helped and sorry for the initial wrong answer. That was me halfway watching TV (Basketball) and halfway reading this place.

As for the Norwegian version, I have no idea. I know that Access will localize itself, but considering my Norwegian is, umm, a bit rusty (as in none at all), I don't know what to tell you there.

 

[Takstein]

Yeah, the only differnece was the fact that it requires ; operators instead of regular commas.. But even in the Norwegian help file that comes along with access they use regular commas....


Anyways.. thanks again..


Cheers,

Arni