2 Replies Latest reply on Nov 16, 2014 12:06 PM by MorkAfur

    SQL LIKE EXPRESSION NOT WORKING

    MorkAfur

      Title

      SQL LIKE EXPRESSION NOT WORKING

      Post

      Having an issue getting FMP to "like" my SQL LIKE wildcard expression.

      The below expression does not find the customer record from the search field with "ON" in it (as in "JONES").

      ExecuteSQL ( "Select count(*) from Customer where UPPER(CUS_LastName) LIKE ?"; "" ; "" ; Upper("%Customer::CUS_SEARCH%"))

      Adding the field name as in the example above seems to prevent the wildcard expression from working. The script debugger shows me that the value of the field in CUS_SEARCH is "ON".

      Yet, in the FMP script debugger, the following works:

      ExecuteSQL ( "Select count(*) from Customer where UPPER(CUS_LastName) LIKE ?"; "" ; "" ; %ON%"))

      So, what's with just adding the field name?

      Using a JDBC connection, I can externally do the LIKE expression fine and even in an external program against the FMP database, but I still am not sure how well supported LIKE is within FMP.

      What's up here?

      Thanks in advance.

      - m