Different flavors of SQL escape text differently see if:
I don't see much reason for setting this up as an optional parameter given that you are using a constant value and not a value from your current record or a variable.
1 of 1 people found this helpful
Try using "%\_FK" as the second search value.
the "_" is a wildcard character (for any single character) with the SQL LIKE, so it cannot be used this way.
I don't believe that ExecuteSQL uses the square brackets that way and I don't believe there are other methods of escaping the character for this type of search.
it might be better if you create a calculated (stored or not) field for such searches (even with ExecutesSQL).
p.s. I see the other answers now. you can try the \_
philmodjunk is right when he says there is no reason to have the constant in a parameter but that's my lack of experience in executeSQL I'm afraid
But I rewrote it as a constant (single quotes in stead of double!) AND used the backslash from David's post. My calculation is now:
SELECT count(column_name) FROM tabe_COLU
WHERE table_name LIKE ?
AND column_name LIKE '%\_FK'
; "" ; "";
and this works OK! The underscore gets selected like a normal character.
Thanks all for your comments and I hope to be able to return the favor.
great! I wonder if the escaped value as a parameter is getting something converted (or not) in FM calc dialog (as that is what is evaluating the ExecuteSQL). It might be worth a try.
At least you know what SQL expects "_" (underscore) to be in the query (a wildcard). ExecuteSQL is not quite like the rest of the SQL's out there, so the square brackets didn't help.
The single quote is how the value is constant inside the query. The double quote is how you pass the literal parameter and FM is supposed to take care of providing the single quote for text values.
Thanks for playing! and passing along the solution!
Actually it's me who has to thank you all in this community and other internet sources for sharing so much filemaker experience and knowhow.
I would not be where I am now had I not be able to find al this shared knowledge in the past three years.