ExecuteSQL ( " SELECT COUNT(*) FROM attendence WHERE job_id_fk = ? AND date = ? " ; " " ; "" ; $$current_job ; $$date )
I can't seem to be able to compare the date, could someone tell me what i have done wrong?
You need double quote there. Single is used for text literal.
WHERE "date" = ?
And need escaped in FM text literal.
WHERE \"date\" = ?
If the variable have date value, GetAsDate() may not need.
How about using
GetAsDate ( $$date )
instead of $$date.
You have to quote any field or table names that are reserved words, and "date" is one.
put the 'date' in, now it returns 0 so solved a problem i still seem not able to get the date format correct
ExecuteSQL ( " SELECT COUNT(*) FROM attendence WHERE 'date' = ? " ; " " ; "" ; GetAsDate($$g_selected_zf_date ) )
the WORD "date" (unquoted) is a reserved word in SQL (and FileMaker). Any word that might break the query needs to be changed (I use myDate or startDate or something appropriately named, but not just date). I see you have your variable named 'g_selected_zf_date'! Or you escape quote as u19752 & Tom showed you.
date -> \"date\"
The document here will give you a list of most of them:
(FileMaker 15 SQL Reference, p. 36+)
because you are using a variable in the parameter, I also will make sure it's the correct type by using:
1. it helps me see what type I'm passing even if the 'name' of the variable has the word "date" in it (as you did)
2. it accounts for even NULL (empty) values - although you should ask for NULL values directly
But that's mostly my preference.
Do you guys know how to have the \"Date\" =? to grab only results for the past 30 days or past 12 months?
\"Date\" > CURDATE-30
Retrieving data ...