try this.. ExecuteSQL ( "SELECT \"assemblyPN\" FROM \"Assemblies\" WHERE \"ASSEMBLYID\" = ?" ; "" ; "" ; structure::LOWER ID )
Prefect - thank you! Clearly I need to understand the syntax further here (the use of \"xxxxxxx\") and I see I should have used the argument parameter.
This works, thanks again!
SQL does not know the field/column "LOWER ID" in the table 'assemblyPN' if the you say structure::LOWER ID is the field.
You can "JOIN" or point to the correct field, but must have the "match" somewhere.
FROM Assemblies, structure
WHERE ASSEMBLYID = \"LOWER ID\"
and \"LOWER ID \" = ?
NOTE: the field with a space must be quoted in the SQL.
Since the names of the fields are unique, you don't need to tell SQL which table for which field/column. But you do need to list the tables where these fields/columns are found!!
Which table/layout are you on when you make this query? Perhaps this query needs to be revised based on another "context" (as FMP knows it)?
ExecuteSQL does not like spaces in field names nor does it like it when you use SQL reserved words as field names. The escaping \" eliminates those types of conflicts that produce a ? result.
Thanks Beverly, this is answered now. I had got into bad habbits with FM and used spaces in a name that SQL does not like. I had forgoten this and didn't understand the need to use the \"escape\".
Your suggestion also helpful.