Hi Everyone,
I am using the ExecuteSQL funtion in FileMaker 13 to set a variable using the LIKE operator with wildcards (%) on either side of another variable like this;
ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; "%" & $$myVar & "%" )
The problem I'm having is that it only returns records from the second letter of each word. For example, let's say I want to search for Purple Unicorn - and my records are as follows;
Purple Polar Bear
Purple Kangaroo
Pink Panther
Blue Mule
White Unicorn
Purple Unicorn
Yellow Bunny
If I "p" I get nothing, but if I type "u" - I will get
Purple Polar Bear
Purple Kangaroo
White Unicorn
Purple Unicorn
Yellow Bunny
but if I type "un" for unicorn I get only Yellow Bunny
I can type "urple" and I will get -
Purple Polar Bear
Purple Kangaroo
Purple Unicorn
I can type "nicorn" and I will get
White Unicorn
Purple Unicorn
But I get nothing if I type "urple nicorn"
Further if I change the wildcard to be $$myVar & "%" or "%" & $$myVar NOTHING is returned regardless of what is typed.
Does anyone know what is going on here. Unless the LIKE operator can be used as intended, I really don't see the purpose!
Thanks,
Amy
Hi Amy,
Just as a reminder: ExecuteSql is case sensitive.
This would explain some of the results that you are seeing, though, unfortunately, not all of the results shown above.
I would have still expected, for instance, 'p' to match on the lower case 'p' in 'Purple'.
On the other hand, case-sensitivity would explain, for instance, why 'un' does not match 'Unicorn', but does match 'Yellow Bunny'.
Apologies if I'm just stating the obvious to you.
Very best,
-steve