8 Replies Latest reply on May 3, 2015 11:48 PM by mananaja

    ExecuteSQL query returning a "?"

    carinahulselmans

      Title

      ExecuteSQL query returning a "?"

      Post

      Hello,

       

      I have a relationship between two tables KASSA(Sales) and DAGOVERZICHTEN(Daily Summaries) on KASSA_AFHANDELDAG(Sales_PayDate) = DAGOVERZICHTEN_DATUM(daily summary date).

      DAGOVERZICHTEN is a table consisting of mostly sum fields.

      Now I've also been asked to have a few sum fields of KASSA in DAGOVERZICHTEN based on a nonexisting relationship of the sales that were made, but not necessarily paid on that day.

      My relationship looks a little something like this : http://gyazo.com/3c2ad320c4592709b504f6d228f7519d

      I tried making a calculation field with an executeSQL query in DAGOVERZICHTEN , so I could avoid the problem that I don't have the correct relationship between the tables (I know this could maybe get solved with the usage of table occurences instead, but then again I don't know how to get 2 table occurences of the same table in one layout, and I'll also be making sums of the nonrelated sumfields + the related sumfields to get a total) 

      My sql query looked a little like this :


      Substitute (ExecuteSQL("select sum(Visjes) from KASSA on KASSA_datum = ?" ;"";"" ;DAGOVERZICHT_DATUM);[".";","])
       

      The substitute is there because I'm working on a european (Belgian) computer & the ExecuteSQL returns general Unicode/SQL formats.

      I tried this out by having a few records with date 4-4-2015 (dd-mm-yyyy) , and opening the daily summary on day 4-4-2015 (dd-mm-yyyy).
      One of the records has the value 112,5 for Visjes, but the query still returns a "?" . I'm at my last resort and I can't figure out how to fix this.
      If anyone could send me in the right direction it would be really useful. I have tried to give as much relevant information as possible, but please ask me for more if needed.

      Yours sincerely,

      Carina.