AnsweredAssumed Answered

ExecuteSQL WHERE Clause with two UPPER clauses

Question asked by taylorsharpe on Apr 17, 2016
Latest reply on Apr 20, 2016 by disabled_morkus

I am working on a FMPA 14 with a file on FMS 14 and doing an Execute SQL where there are two UPPER clauses in the WHERE statement as below.  This results in a "?".  However, if I take out one of the UPPER clauses, then it works.  I tried one with UPPER and one with LOWER and that didn't work either.  So it looks like in the WHERE part of the SQL statement, you can only use UPPER or LOWER once and not more than once.  It sure seems like a bug to me.  My work around was to make a calc field in the People table of the last name that made an UPPER via a FileMaker calc stored field.  Anyone with a better suggestion or workaround?

 

Let ( [

F1 = WebCheckIn::NameLast ;

F2 = WebCheckIn::NameFirst ;

F3 = "SELECT

               ID

          FROM

               People

          WHERE

               UPPER ( \"Name First\" ) LIKE ? and

               UPPER ( \"Name Last\" ) LIKE ? " ;

F4 = ExecuteSQL ( F3 ; ¶ ; ¶ ; "%" & Upper ( F2 ) & "%" ;  Upper ( F1 ) & "%" )

] ; F4 )

Outcomes