3 Replies Latest reply: May 15, 2014 9:58 AM by Bill Watson RSS

    Filtering a text field

    ron cousino

      I use Monarch Pro V9.01 and am having a problem in filtering out records using the .NOT. operator.  My expression is built as follows:

      .NOT. ="** DO NOT USE  **".  Here is the example from the report [font="courier"] MUTUAL GROUP          ** DO NOT USE **

      N.W. SHEET METAL          ** DO NOT USE **

      NATIONAL AMERICAN          ** DO NOT USE **

      NATIONAL GROUP LIFE          ** DO NOT USE **

      NATIONAL STUDENT SERV          ** DO NOT USE **

      NATIONWIDE INS          ** DO NOT USE **

      NEW YORK LIFE     SUITE 600     ** DO NOT USE **

      NHSRA          ** DO NOT USE **

      NORTHWESTERN NATIONAL          ** DO NOT USE **

      NW NATIONAL          ** DO NOT USE **

      OCCUPATIONAL HEALTH MNGMT          ** DO NOT USE **

      /font[/quote]

        • Filtering a text field
          RalphB _

          Ron,

           

          Have you tried the instr() function?  You could set up your filter something like:

           

          instr("** DO NOT USE **",[ADDRESS 2])=0.

           

          If you want to include the phrase, change it to <>0.

           

          HTH.

           

          Ralph

          • Filtering a text field
            Data Kruncher

            Technically there's nothing wrong with your filter expression Ron. It should work just fine.

             

            Perhaps your address field is picking up a leading space or two and that's why it's not filtering properly.

             

            While Ralph has presented a perfectly usable alternative, you might want to try:

             

            [font="courier"]Trim()<>"** DO NOT USE **"[/font][/quote]Changing the .NOT. and = to <> is strictly personal preference. I find it easier to read. Both would work just fine.

             

            Kruncher

            • Filtering a text field
              Bill Watson

              Originally posted by Data Kruncher:

              Technically there's nothing wrong with your filter expression Ron. It should work just fine.

               

              Perhaps your address field is picking up a leading space or two and that's why it's not filtering properly.

               

              While Ralph has presented a perfectly usable alternative, you might want to try:

               

              [font="courier"]Trim()<>"** DO NOT USE **"[/font][/quote]Changing the .NOT. and = to <> is strictly personal preference. I find it easier to read. Both would work just fine.

               

              Kruncher /b[/quote]There is of course the fact that * may be being treated as a wild card, although I wouldn't have thought that would make a difference in this case. You could try stripping out the *'s and trying with simply "DO NOT USE"