AnsweredAssumed Answered

ExecuteSQL query returning a "?"

Question asked by carinahulselmans on May 2, 2015
Latest reply on May 3, 2015 by mananaja


ExecuteSQL query returning a "?"




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 :

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,