Count fields greater than zero using expressionbuilder

[emperial]

Hi all,

Please help with the above issue as I am absolutely desperate.

There should be a simple solution.

I am working on timesheets where the fields Saturday - Sunday have different jobnumbers.

Example:
Sat Sun Mon Tues Weds Thurs Fri Days
10 12 08 07 06 13 0 6

At the end of every row is a column called 'days'.

I would like to count the days everyone has worked, but EXCLUDE the 0 (as this represents a day off).

The expression I am using at the moment is:
=(IsNull([Sat]))+(IsNull([Sun]))+(IsNull([Mon]))+(IsNull([Tues]))+(IsNull([Weds]))+(IsNull([Thurs]))+(IsNull([Fri]))+7

(as I couldn't get the 'isnull' to work).
However, this returns 7 in the days 'field'. But it needs to be a 6 as I do not want to count cells that include 0.

Please can someone help?

Let me know if you need any more information.

Cheers

[twinnyfo]

If your value is truly 0 (zero), then the field is not null. For whatever reason, you are adding 7 to the end of your expression, so unless any values are actually null, you will get 7, but any Null values will decrement by 1 (as "True = -1"). Do you want to count the days that are not 0 or sum the values of those days? To count days:

=IIf([Sat]<>0,1,0) + 
    IIf([Sun]<>0,1,0) + 
    IIf([Mon]<>0,1,0) + 
    IIf([Tues]<>0,1,0) + 
    IIf([Weds]<>0,1,0) + 
    IIf([Thurs]<>0,1,0) + 
    IIf([Fri]<>0,1,0)

Hope this helps

[emperial]

twinnyfo, thank you so much for your prompt reply. This code works perfectly and is exactly what I needed.

I wanted to count the days worked (=>0), excluding days off (=0).

Massive massive thanks!

[twinnyfo]

emperial,

Glad I could be of assistance. hope to see you around here again!

[DJRhino1175]

I'm doing something similar, but its not working for me. Here is what I put:

=Count(IIf([ChangeOverMinutes]<>0,1,0))

I only want a count of the records that are greater than zero.


Never mind I have it figured out now.