3 Replies Latest reply: Jul 29, 2011 12:28 AM by Erich Shiino RSS

    If Statement and Null Value Problems

      Hi guys, I have a small issue with an If statement and null values.


      I have a range of values which are banded up based on another field.


      The original field contains numbers between 0 and 15. The requirement is to include only numbers between 0 and 10 and exclude all numbers above 10.



      IF([Value 1] > 0 and [Value 1] < 5, '< 5',

           IF([Value 1] >= 5 and [Value 1] <10, >5'))  as New_Value.


      I have no else part in my If statement.


      When I use my new field called New_Value in a table, I try to calculate percentages and I find out that my percentages don't sum up to 100% when I use only <5 and >5, because there are some null values which correspond to values above 10 that I have excluded in my load script.


      Is there a way I can get rid of all the unwanted values above 10, so that when i work out percentages in my table, my totals will sum up to 100% for only values between 0 and 10?



      Thank you