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 : 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.