Report Using =sum(iif([Exp Hours]>0,))

[hikerdood]

Trying to sum only positive values in Report field [Exp Hours] but get zero as a result.



If I use =sum([Exp Hours]) is works but nets the hours.

I did some research and used the formula in the title.

Any help is appreciated.

Regards

Hikerdood

[ranman256]

in the query ,put the critiera: [Exp Hours]>0
then in the report (or other query) SUM

[hikerdood]

ranman,

Sincerely apologize for using the wrong field for summation. I should have used the [Bal Hrs] field when trying to sum. The [Bal Hours] takes the [Issued Hrs] less the [Exp Hours] and it is the [Bal Hours] I want to sum. We have a culture of sometimes overcharging Job Orders and we want to show not only positive balances remaining for each open individual Job Order record but also show the Departments each open individual Job Order record that has a negative in [Bal Hours].

What I am attempting to do is get a total [Bal Hours] at the group and footer level for only those open Job Orders with a positive balance and not sum the net hours with the inclusion of negative hours. Hope I am making sense. This is what I thought would work in the reports [Unbound] text box at the footer level but it doesn't. I got this format from a search.



=sum(iif([Bal Hours]>0,0))



Regards

hikerdood

[Micron]

I might try DSum with criteria. Not sure if the domain has to be a table or query, or if you can refer to your report field and that would be based on it's recordset.
Or you can post a db copy. I often muddle my way through the complexities of report requests but extracting that from my old noodle hurts too much!

[Bullschmidt]

What I am attempting to do is get a total [Bal Hours] at the group and footer level for only those open Job Orders with a positive balance and not sum the net hours with the inclusion of negative hours. Hope I am making sense. This is what I thought would work in the reports [Unbound] text box at the footer level but it doesn't. I got this format from a search.

=sum(iif([Bal Hours]>0,0))

Try correcting the above to be this instead (I just tested with something similar and worked for me):
=sum(iif([Bal Hours]>0,[Bal Hours],0))