Iif calculations in a form

[BEI77]

I have a field named SickDaysTaken and one named ExcessSickDays. I want to give 3 days of Sick Days with no penalty and I want the calculation to start


adding to the ExcessSickDays field after the number 3 has been entered into SickDaysTaken.

My thought is an expression for ExcessSickDays field would be

IIF([SickDaysTaken]<=3,"0",IIF([SickDaysTaken]>3,[SickDaysTaken]*1))

Any help appreciated, Thanks

[ranman256]

Don't make zero a string,,,just 0.
you don't need '*1', just use [sickDaysTaken]

[kd2017]

Do you not have an extra IIF statement in there? Anything wrong with ExcessSickDays: IIF(SickDaysTaken<=3, 0, SickDaysTaken-3) ?

[BEI77]

I get a syntax error when I try to save it.

[BEI77]

kd2017 that worked, except I need it to subtract from the score. Up to 3 days is no points against them and will add to their score. Over 3 days will subtract from their score.

[kd2017]

Okay, I don't understand the full context of what you're trying to accomplish.

SickDayScore: 3 - SickDaysTaken

[BEI77]

Sorry

SickDayScore if you take 3 days 3=3
2=+1
1=+2

4=-1
5=-2 ect.

[kd2017]

SickDayScore: IIF(SickDaysTaken <= 3, SickDaysTaken, 3 - SickDaysTaken)

In this scenario Zero days taken equals zero add to score while 3 days taken equals 3 added to score. 3 sick days taken better than none?

[BEI77]

I got it thanks all.