IIf statement in a report

  • Thread starter Thread starter luvarchaeology via AccessMonster.com
  • Start date Start date
L

luvarchaeology via AccessMonster.com

I am having a problem with the following iif statement:

=IIf(Sum([Hours])>0,(Sum([Hours]))-40,"0")

I have a query based report that returns the number of hours worked per
project each day for each employee. The total hours for the week is then
totaled in the text box "txtWeeklyHours" with the statement: Sum([Hours]).
[Hours] refers to a field in the query that the report is based on.

My goal is to have a text box display the hours of overtime each employee has
for the week, if the total weekly hours exceeds 40. To do this I created a
text box title "txtOvertimeHours" and inserted this iif statement in the
control source. It works if the hours are over 40, but rather than displaying
a 0, if the hours are 40 or under, it displays a negative number.

How can I fix the statement so that a zero is displayed for all totals 40 and
under?
 
I'm not sure why you would return a text value "0" from your IIf()
expression.
Try:
=IIf(Sum([Hours])>40,Sum([Hours])-40,0)
 
Back
Top