5 Replies Latest reply on Aug 6, 2013 11:50 AM by raybaudi

    Find records with text containing more than 10 characters

    Corné

      Title

      Find records with text containing more than 10 characters

      Post

           Hello all,

           I need to filter a column with text, in such way that only text with more than 10 characters are filtered. I tried to set Length (field) > 10 as a search criterium, but this didn't give any good result. Can you please help me out? Thanks in advance!

           Best,

           Corné.

        • 1. Re: Find records with text containing more than 10 characters
          Corné

               I have found a solution, not the way I intended through a calculation, but when I set @@@@@@@@@@@* (11x @ and 1 asterisk) as a the search criterium it shows me all 11 character items and longer.

               Any suggestions how I can do this with a calculation (like Length(Field) > 10) are more than welcome...

               Corné.

          • 2. Re: Find records with text containing more than 10 characters
            philmodjunk
                 

                      the search criterium it shows me all 11 character items and longer.

                 That's exactly what you said you were trying to find, all records where the length of the field is more than 10 is exactly the same as "11 character items and longer".

                 What result do you need to see for this find?

            • 3. Re: Find records with text containing more than 10 characters
              Corné

                   Hi Phil, thanks for chiming in.

                   The result is exactly what I was looking for, I was just hoping to find a more 'smart' solution e.g. a calculation so I could make use of a variable to replace '10' for another value if needed.

              • 4. Re: Find records with text containing more than 10 characters
                philmodjunk

                     Ok, try this one on for size. Say you enter/select that number in a global field named Globals::gLength. (Global fields can be defined in any table and this will still work.)

                     Enter Find Mode []
                     Set Field [YourTable::YourField ; Substitute ( 10^Globals::gLength - 1 ; 9 ; "@" ) & "@*" ]
                     Set Error Capture [on]
                     Perform Find []

                     How it works:

                     ^ is the exponential power operator so 10^1 = 10, 10^2 = 10 * 10 = 100, 10^3 = 10 * 10 * 10 = 1000 and so forth.

                     Subtract 1 and you get a 9 for each power of 10 specified as your exponent: 10^1 - 1 = 9, 10^2 - 1 = 99, 10^3 - 1 = 999 and so forth.

                     Substitute replaces every 9 with @ and then we use an concatetnation operator (&) to tack on one more @ and the * operator to produce the needed string of wild cards for your find.

                • 5. Re: Find records with text containing more than 10 characters
                  raybaudi

                       Isn't simpler to create a new calculated field with calc: Length ( field ) and then search using it ?