1 2 Previous Next 22 Replies Latest reply on Apr 28, 2017 9:21 AM by beverly

    How to convert text to number for FMP's ExecuteSQL?

    fmpdude

      This may be simple and I've missed it, but  how do you treat a text field as a number in FMP's SQL so it doesn't give you an error?

       

      ---

       

      Assuming col1 and col2 are TEXT, the following SQL gives me an error in FileMaker that the input type for the first parameter (as in "AVG") is the wrong type:

       

      SELECT AVG(COL1-COL2) from TableName

       

      FMP ERROR BELOW:

      (of course in FMP, this is just a "?" Good luck figuring that one out from the "?")

       

      ---

       

      Now, the exact same query in MySQL with varchar field types works perfectly with no errors AND no conversion needed, either:

       

      Data:

       

       

      Query:

        1 2 Previous Next