AnsweredAssumed Answered

ExecuteSQL calculation from entered date to start date

Question asked by gremlin9297 on Aug 6, 2015
Latest reply on Aug 7, 2015 by gremlin9297


ExecuteSQL calculation from entered date to start date


Hi there,

I have a ExecuteSQL,

("SELECT sum(vs.sick) FROM Vacation_Sick vs WHERE year(vs.\"Date\") = ? AND vs.\"Ee ID\" = ? " ;"" ; "" ; Year(Current_Date) ; Ee ID )

The calculation works.. for the most part. The problem is that when a date is entered that does not match up with a date already listed the field remains empty. I understand why it does but I was wondering if there was a way for the calculation to work even if there are no dates. Such as 

1/2/15 - 1

2/1/15 - 1

5/6/15 - 1

I get the total of 3 based off the current date.

I have a report summary that needs to do the same calculation but instead of working off the current date it works off a report date thats entered.

("SELECT sum(vs.sick) FROM Vacation_Sick vs 
WHERE Year (vs.\"Date\") =  ? 
AND Month (vs.\"Date\") =  ?  
AND Day (vs.\"Date\") =  ? 
AND vs.\"Ee ID\" = ? " 
;"" ; "" ; Year(gReport_Date) ; Month(gReport_Date) ; Day(gReport_Date) ; Ee ID )

.... I get an empty field if I enter a date past 5/6/15. Any help would be appreciated.

Thank you,