A ? results means that you have incorrect SQL syntax and this is the case here as your use of "on" is incorrect. that would be part of a Join clause that you do not set up here for your "non-existent" relationship. But I suspect that you might be able to get the results you want just using WHERE.
See this document as a resource on FileMaker SQL syntax: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
And see here for a FileMaker file that can help you write an SQL query with correct syntax: http://www.seedcode.com/cp-app/ste_cat/sqlxfree
but then again I don't know how to get 2 table occurences of the same table in one layout,
You could put the records from the second occurrence into a portal or, instead of duplicating the layout's table occurrence, duplicate the table occurrence on the other side of the relationship.
Ahh, the "on" was a remainder of past tries using joins that also didnt work out.
"where" however still returns a "?" , so does using "JOIN ON KASSA_datum = ?"
Which is why I referred you to two sources on correct FileMaker SQL
"JOIN ON KASSA_datum = ?"
BTW, is incorrect syntax for a join clause.
I'm sorry, I answerred too quickly.
I meant "FROM KASSA INNER JOIN DAGOVERZICHTEN ON KASSA_datum = ?"
The second link you provided leads me to a nonexisting page, and I've read the first one before posting this post, I'm reading it again now just to make sure I didn't miss anything.
EDIT : I've also tried joining both fields like this :
Substitute (ExecuteSQL("select sum(Visjes) from KASSA INNER JOIN DAGOVERZICHTEN ON KASSA_datum = DAGOVERZICHT_DATUM" ;"";"" );[".";","])
Still returns a "?"
And that still is incorrect syntax. Please look at the Join clause examples in the PDF for which I have already shared a link.
A join clause must specify both the table names and match fields separated by an operator. Unlike a regular calculation, you use a period to separate the table name and field names in the reference to that field and you have the option of using an alias in place of the table name.
You can't leave out the table names. You can't use the optional parameter as this will insert a value where you need a table.fieldname reference.
But I suspect that you might be able to get the results you want just using WHERE
I think what PhilModJunk said may work with what you originally had. Pleas try this:
Substitute ( ExecuteSQL( "SELECT SUM(\"Visjes\") FROM \"KASSA\" WHERE \"KASSA_datum\" = ?" ; ""; ""; DAGOVERZICHTEN::DAGOVERZICHT_DATUM); [".";","] )
Thanks for all solution. I have enjoyed with this forum.