4 Replies Latest reply: May 15, 2014 10:15 AM by Chickenman _ RSS

    Erratic Filter Behavior

    Chickenman _

      Seemingly simple situation, field is numeric and nulls represented as blanks. Want all records with any number in the field, so MyField>0; this returns nothing. Change to MyField space[/B] > space[/B] 0 and it filters OK. Change to other filters, come back to this one and nothing. Also filter .Not. IsNull(MyField) returns nothing.


      In Options -> View "Display n[/U]ull values as" changed from blank to 0 still doesn't improve.


      What am I missing here?



        • Erratic Filter Behavior
          Grant Perkins

          Hi CM.


          Not had to think about this one for a while but usually filtering whilst playing with NULLs is prone to some challenges. Changing the display to 0 only affects the display, not the underlying data.


          The simplest ans most visibly traceable approach is likely to be to create a calculated field that duplicate you desired filter field values EXCEPT for recording 0 for NULL fields. That then gives you an all numeric proxy field that you can use for sorting, filtering, whatever you may need to do with it. Hide one or other of the fields (or not, your choice there!) for final use and you should be in good shape - ASSUMING that no 'genuine' non-Null fields would have a zero value of course.


          ISNULL gives you a binary output (0 = "Not Null" or 1 = "NULL Value Field") for a status. If you simply want to filter out NULL fields that might work for you. The embedded Help file has an example that seems pertinent to your needs.  IsNull()=0 should return all fields that are not[/B] NULL.