Pretty sure that "date" is reserved keyword. You should not use field names that are the same as data types.
Date is a reserved word (it's a function in SQL and in FMP)! the field names beginning with "_" have to be quoted, too.
Cathy, you need to quote the entire query:
ExecuteSQL ( "query" ; "" ; "" ; argExpr ; argExpr )
IF the query is in a field then this works:
ExecuteSQL ( Quote(table::queryField) ; "" ; "" ; argExpr ; argExpr )
IF the query is not in a field, then you probably need to escape your inner quotes:
"SELECT \"Date\", \"_InvNo\", \"GrossValue\"
WHERE \"Balance Due\" >0 AND \"_AccNo\" =?" ;
; 5737 )
You ONLY need to escape reserved words and words beginning with "_" (and a few others). So GrossValue and Invvoice_Customer are ok as is. If it helps to quote all fields and tables, then do so. The Quote(field) function takes care of the escaping for you.
+ The arguments (5737, in this case) are in DOUBLE QUOTES, too, unless they are numbers. FileMaker will take care of the single quotes as needed.
Did you see my article and example file? "The Missing FM 12 ExecuteSQL Reference"
There are references to all kinds of other resources, too.
Hi Beverley and thanks
I knew I had seen a reference and after my post I did manage to find it and have saved it
It also saved me as I could see immediately from that I was missing the \ characters.
I have now got it workiing and am reading through the rest of your reference which is excellent. I had downloaded previously which is how I knew of its existence but I lost it
Thank you VERY much