So you want to get a total number of days but only from those records where the date is from the current year.
SELECT Sum ( NumberOfDay ) FROM YourTableOccurrenceNameHere
Year ( RecordDate ) = ?" ;
"" ; "" ; Year ( Get ( CurrentDate ) ) )
The calculation returns same value in all records, so the calculation field may be better defined as global or unstored or in another table or use script to calculate, not calculation field.
If there are spaces in field name, you need to quote it like \"Number of day\"
Using range in WHERE may be better for performance.
RecordDate >=? AND RecordDate <=?
Hello, I tries follow what you said, but it showing question in the result.
ExecuteSQL is very picky about field and table occurrence names.
Please post exactly what you have attempted to set up and then list the exact field and table occurrence names. A table occurrence is a "box" in your relationship graph that, in turn, refers to one of your tables.
Hello again and thanks for your help.
for example, in staff profile form , it will about the permission record like below
Permision Date Type # of day
1-Jan 2015 Annual Leave 3
2-Jan 2015 Sick Leave 3
15- Jan-2016 Annual Leave 4
23-Feb 2016 Annual Leave 2
4 - Mar 2016 Maternity Leave 14
Let say I want to create calculation field to calculate how many day of this staff used Annual leave in the current year(2016)
If you still need help, please post the expression that produced a question mark result.
Hello, sorry that late.
In the result it shows [?]
1 of 1 people found this helpful
I would recommend you use an external tool, like RazorSQL, to get your queries working. FileMaker's brain-dead data viewer (a "tool" needing, IMHO, a total rewrite) is really almost no help at all. The data viewer doesn't help you write your queries with pop-up assist, it doesn't give error messages (? is it's best advice), and it has other limitations. Yuck!
The data viewer is "OK" for really simple stuff, but it fails miserably for any serious SQL work, that is, unless you have lots of time and plenty of frustration handling ready.
Using an external tool is a joy in comparison. I find I get queries working much faster and with MUCH less frustration!
Once you get your queries working, then move them to FileMaker and add replaceable parameters and the like.
NOTE: The external tool, like RazorSQL, IS CONNECTING TO YOUR ACTUAL FILEMAKER DATABASE IN REAL TIME.
HOPE THIS HELPS.
Several posts ago, I asked you to post exactly what you tried. I was asking for the actual calculation as you had it set up as a call to ExecuteSQL. Had you done that we might have been able to spot what was producing the ? result--which typically indicates a syntax error in the SQL query.