[b]Query Formula for subtracting without negative results[/b

[Smurfit-Stone]

I need help with a formula for calculating points. Employees are given points for things like: absent=1 point, late (up to 1 hour)=0.25, late (more than 1 hr, but less than 4)=0.50 point etc. If an employee has no infractions for 60 days, a point is subtracted from their total points. My problem is that I need a query that calculated this and removes a point after 60 days and if the employee only has 0.50 total, it doesn't create a negative result. I'm guessing I need some kind of nested IF Statement. Please help. I need this fix by tomorrow. Currently the formula in the query reads: Totalpoints: IIf([Notified]<>False,[Points],[Points]+1). I inherited this database and I'm not sure what this formula does.?????? Thanks Board.

 

[Andrew Fergus]

Re: [b]Query Formula for subtracting without negative result

Hello and welcome to Mr Excel.

It appears your question has "slipped through the cracks", probably due to a lack of information. Given your impending deadline I will have a go at answering your question but please note that I'm going to have to make a few assumptions, so if I am way off track can you please fill in the gaps?

I'm assuming your existing formula :

IIf([Notified]<>False,[Points],[Points]+1)

...sort of works but it is giving a negative result if the employee has less than one "infraction point". { <- This might be a really bad assumption but I have start somewhere and this is what you provided}

I'm also assuming the points are stored as negatives given your formula has +1 under the else condition {If this is not correct then you will need to change the sign of my formula - although I can't see how they are stored as negatives if you don't want "a negative result" but this doesn't fit with the +1 per the formula, so I'm confused}

I don't know what the [Notified] field is but I'm going to assume this is a field with a value of either True or False and if the employee has had points awarded in the past 6 months then the value of this variable does not equal false (per your formula) and that if they have not had any points awarded in the past 6 months then the value of [Notified] equals False.

I'm also assuming that the point is only deducted if there are currently no infractions, but if additional points have been awarded then the +1 bonus is lost. {If this is not the case then you will need to store the +1 bonus via an append query instead}

So, with those assumptions in mind try this :

IIf([Notified]<>False,[Points],IIf([Points]<-1,[Points]+1,0))

My gut feeling is that this is way off track and if so, can you please correct me where I have made some bad assumptions?

HTH, Andrew.

 

[Smurfit-Stone]

Re: [b]Query Formula for subtracting without negative result

Thanks Andrew93, I've been gone for awhile, I'll try the formula and let you know if it works.....I didn't build this app. and I'm still trying to figure it out,......Thanks again!