By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

Count fields greater than zero using expressionbuilder

P: 9
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
Jul 22 '14 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. =IIf([Sat]<>0,1,0) + 
  2.     IIf([Sun]<>0,1,0) + 
  3.     IIf([Mon]<>0,1,0) + 
  4.     IIf([Tues]<>0,1,0) + 
  5.     IIf([Weds]<>0,1,0) + 
  6.     IIf([Thurs]<>0,1,0) + 
  7.     IIf([Fri]<>0,1,0)
Hope this helps

Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
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:

Expand|Select|Wrap|Line Numbers
  1. =IIf([Sat]<>0,1,0) + 
  2.     IIf([Sun]<>0,1,0) + 
  3.     IIf([Mon]<>0,1,0) + 
  4.     IIf([Tues]<>0,1,0) + 
  5.     IIf([Weds]<>0,1,0) + 
  6.     IIf([Thurs]<>0,1,0) + 
  7.     IIf([Fri]<>0,1,0)
Hope this helps
Jul 22 '14 #2

P: 9
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!
Jul 23 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
emperial,

Glad I could be of assistance. hope to see you around here again!
Jul 23 '14 #4

100+
P: 163
I'm doing something similar, but its not working for me. Here is what I put:
Expand|Select|Wrap|Line Numbers
  1. =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.
Oct 19 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.