3 Replies Latest reply on May 13, 2015 7:45 AM by philmodjunk

    Issue with ExecuteSQL



      Issue with ExecuteSQL


      Hi, i have a problem with a field, part of the table "Projection", which is defined with the following calculation:

      ExecuteSQL("SELECT COUNT(*) FROM Projection ";"";"")

      This calculation works perfectly, and gives as result the total number of records, but instead if i put

      ExecuteSQL("SELECT COUNT(week) FROM Projection ";"";"")

      It returns the "?" even if the syntax is correct (the field "week" exists and is part of the same table "Projection"). No way to make it work, even with the syntax \"week\"

      I am very frustrated, i can't understand where is the problem.

      I know that i could get the same result using Filemaker's functions instead of ExecuteSQL, but this is only the first step of a more complex calculation that involves many ExecuteSQL steps so i would like to understnd why it doesn't work


      Thank you in advance




        • 1. Re: Issue with ExecuteSQL

          It works for me in FileMaker 13 Advanced when I test this in the data viewer. I tested this with and without quotes around the field and table names.

          • 2. Re: Issue with ExecuteSQL

            Thank you Phil for making the test for me. Actually i don't understand why it doesn't work, i have FM13 (not advanced) and i'm 100% sure that the syntax of the field is correct. Maybe is because of some option of the field itself? By the way, it is the same you did in the test: 1,2,3,4...52


            • 3. Re: Issue with ExecuteSQL

              I'd check to make sure that your field and tables names are exactly correct. Make sure that you don't have an invisible character that is part of your defined field/table names that then is omitted in the query. Might just be a space at the beginning or end of your field name, for example.

              If you had FileMaker Advanced, I'd recommend a custom function that I discovered that can replace the ? result with actual error messages when used in the data viewer. (Still waiting on my download for 14 advanced, who know? maybe they've replace the ? with error messages in that version...)