Function in Query

[Unicon]

I have a query where I have a field NormalDays and SickDays. I want to calculate if someone has Less than 26 days than it will take rest days from SickDays like below

NormalDays = 24
SickDays = 4
I put function like IIF([NormalDays]<26,[NormalDays]+[SickDays],[NormalDays])
But in this case I want query to calculate only 26 days if someone works for 24 days + 3 SickDays my function will say 27 days but I want to give him only 26 days not more than that.
Is there any way out to fix this problem? Please advice.

 

[bob fitz]

Hi
This is untested, but you may like to try:
IIF([NormalDays]<26,IIF([NormalDays]+[SickDays]>26,26,[NormalDays]),[NormalDays])

 

[Unicon]

Hi Bob
Thanks a lot,

My whole concern is if someone works less than 26 days than I need to get those days from SickDays you can say
NormalDays = 25
SickDays = 4
I want to take only 1 day from that SickDays which will make 26 days. I want query to show that value 1 day.
NormalDays = 23
SickDays =5
I want to take 3 days from that SickDays. Here I want query to show 3 days to make it 26 days.
Please advice as I am really struggling for this.
Once again thanks for your help.

 

[bob fitz]

Hi
I'm not really sure what result we are trying to get but I think this may be what you need.

IIF([NormalDays]<26,IIF([NormalDays]+[SickDays]>26,[NormalDays]+[SickDays]-26,[NormalDays]),[NormalDays])

 

[Unicon]

Thanks Bob,
This is working