3 Replies Latest reply: May 15, 2014 10:04 AM by Nick Osdale-Popa RSS

    Wildcard and Instr Problems

      Hi,

       

      I am trying to filter my data based on a text field - TRAN. I want to keep those records where the text field contains "TRANSFER".

       

      I tried using wildcards: TRAN = "TRANSFER"

      All records were returned.

       

      I tried using Instr: Instr(TRAN,"TRANSFER") > 0

      Zero records were returned.

       

      Then I created a calculated field using the wildcards above:

      If(TRAN = "TRANSFER", 1, 0)

      All records returned a 1, even those without "TRANSFER" in them.

       

      Then I created a calculated field using the Instr function above:

      If(Instr(TRAN,"TRANSFER")>0, 1, 0)

      All records returned a 0, even those without "TRANSFER" in them.

       

      If I created a calculated field using Instr and enter the entire field:

      Instr(TRAN,"ACCOUNT TO ACCOUNT TRANSFER")

      The number 1 would be returned of course for those that matched entirely and 0 for the rest. However, TRAN field may have numerous phrases with "TRANSFER" in it, so I don't want to have to find and enter all of them.

       

      HELP!

       

      Thanks,

      JSR

        • Wildcard and Instr Problems

          Sorry...forgot to let you know, I am using Pro 10.

          • Wildcard and Instr Problems

            I feel so humiliated...I played around some more and found I mixed up my arguments with Instr function...once I switched them I was golden:  Instr("TRANSFER",TRAN)>0

             

            However, I would still like to know why the Wildcards are not working.

              • Wildcard and Instr Problems
                Nick Osdale-Popa

                However, I would still like to know why the Wildcards are not working.[/QUOTE]

                 

                That's a very interesting question.

                 

                I tried a filter to look for vendors:

                = "TAY*"

                 

                It found one occurrence for Taylor

                 

                I tried

                = "?AY*"

                 

                It found 4 occurrences:

                Taylor...

                Day...

                Jay...

                Ray...

                 

                I then tried

                = "AY"

                = "*AY"

                 

                Each returned all records.

                 

                So it seems that if the "*" is the first character in the match, it disregards all trailing characters, thus matching all occurrences. Don't know if this is by design or a bug. I'd go with the latter as it's not performing as one would think it should.

                 

                 

                In searching the Help File, the only place I found that even mentions the way the * wildcard should work is when searching text in the Tree View.