It's a good practice to enclose all field and table names in double quotes. If you didn't really need them, no harm is done, but SQL can have an issue with certain characters such as a leading underscore and some field names (such as status, I would guess) can be the same as a SQL reserved word and thus must be quoted to be treated as the name of a table occurrence or field instead of a reserved word.
WHERE \"status\" = 'Paid'
the \" combination allows you to insert a double quote character inside a quoted string such as the text used for the query in ExecuteSQL.
Thanks. If I have an ExecuteSQL function in my calculation for say a record in my invoice table, how can I reference that particular invoice number field in my ExecuteSQL? is that what the documentation means by dynamic?
Use the ? character in your SQL and then include a reference to that field as an optional parameter after the record and field separator parameters. See the example at the end of the article on ExecuteSQL that you can find in FileMaker Help for how to set that up.
thanks as always
An interesting thing is happening with this ExecuteSQL statement:
following the example above - if "...WHERE \"status\" = 'Paid'" refers to a field status that is a calculation, the statement returns ?
when i went back to my status calculation, I had it returning a number and not text - when I changed the calculation in the status field to return text, the ExecuteSQL worked great!