Formula and max value problem

[romeyp]

I have a report I am trying to complete.
This is the line that I am having a problem with.
=IIf([Expr2]>=240,240,[Expr2])
This line feeds information into a text box on my report.
Here is how it works -
It is taking data from a vacation/sick report in the same database Expr2 represent the amount of sick hours a person has accrued with 240 being the max. If expr2 is >=240 then show 240 in the text box, if not then show the current balance.
My problem is that each month people accrue 4 more hours which pushes them even farther over the max of 240. If more than one month has passed and they have accrued more hours then they have taken the amount never drops below 240 and they stay maxed out, when it is suppose to subtract what ever they have taken from 240 and show the new balance.
How do I get this to work as follows
If Expr2>=240,then 240-taken amount, else Expr2.
What would really help is if someone would tell me how to set the max at 240 and the to have the current taken amount subtracted from 240 if they have reached their max.

I know this looks like a mess, but you guys have helped me with bigger messes than this, so I'm counting on you once again.

Thank you in advance

 

[dhookom]

Is Expr2 a field from your report's record source? If so, do yourself a favor and give it a decent alias. "Expr2" is not too expressive.

The expression should work as you have as long as the name of the text box in the report is not also the name of a field/column. What doesn't work with the current expression?

What does the Expr2 expression look like in the query?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[romeyp]

Duane,

Yes Expr2 is a field in my record source, and you are correct that I should rename it to be able to reconize it better (something I should have done over a year ago when I created this database).

A quick fix for me would be if I could just subtract the sum of sick time taken from 240 if someone has reached the max amount of hours which is 240.

When currently try to do this I get the request box asking for parameters?

Any help would be appreciated.

Thank you for your reply.