I read the FM v16 SQL Reference (Page 9) but I don't think it gave me the whole skinny on using non-alphanumeric field names so I'm stuck. Using this formula works:

ExecuteSQL ( "SELECT COUNT ( k_StudentID ) FROM EXPERIENCE WHERE k_StudentID = ? " ; "" ; "" ; k_StudentID )

...but the real name of the field is _k_StudentID, so the leading underscore character is the troublemaker.

In the Guide it says to use double-quote marks but FileMaker Pro squawks with an error message when I use them so I'm missing something. What's the correct syntax to make this formula work?

Thanks in advance for your help.

Edited April 5, 20187 yr by TeacherEd

you need to escape fields with underscores..

ExecuteSQL ( "SELECT COUNT ( \"_k_StudentID\" ) FROM EXPERIENCE WHERE \"_k_StudentID\" = ? " ; "" ; "" ; k_StudentID )

 

Thanks! (For anybody copying and pasting Ocean West's formula and finding that it doesn't work, it's because the third k_StudentID fieldname is missing its leading underscore character; _k_StudentID works.)