5 Replies Latest reply on Mar 1, 2016 1:17 PM by jgomes384

Calculation Evaluates as True Always

I'm having trouble with this calculation:

Case ( DayOfYear ( Date ( 2 ; 29 ; PTOCalendar::Att_Year ) ) = DayOfYear ( Date ( 3 ; 1 ; PTOCalendar::Att_Year ) ) ;

ExecuteSQL ( "SELECT Sum ( Hours ) FROM AttendanceRecords WHERE EmpID = ? AND RecDate = ?" ; "" ; "," ; Extend ( PTOCalendar::E_ID ) ; Date ( 12 ; 31 ; Extend ( PTOCalendar::Att_Year ) - 1 ) + Get ( CalculationRepetitionNumber ) )

;

Case (

Get ( CalculationRepetitionNumber )  <  60

;

ExecuteSQL ( "SELECT Sum ( Hours ) FROM AttendanceRecords WHERE EmpID = ? AND RecDate = ?" ; "" ; "," ; Extend ( PTOCalendar::E_ID ) ; Date ( 12 ; 31 ; Extend ( PTOCalendar::Att_Year ) - 1 ) + Get ( CalculationRepetitionNumber ) )

;

Get ( CalculationRepetitionNumber ) = 60

;

0

;

Get ( CalculationRepetitionNumber ) > 60

;

ExecuteSQL ( "SELECT Sum ( Hours ) FROM AttendanceRecords WHERE EmpID = ? AND RecDate = ?" ; "" ; "," ; Extend ( PTOCalendar::E_ID ) ; Date ( 12 ; 31 ; Extend ( PTOCalendar::Att_Year ) - 1 ) + Get ( CalculationRepetitionNumber ) - 1 )

)

)

There's a lot there so, I'm specifically having trouble with the first Case test: DayOfYear ( Date ( 2 ; 29 ; PTOCalendar::Att_Year ) ) = DayOfYear ( Date ( 3 ; 1 ; PTOCalendar::Att_Year ) )

For some reason this statement is always evaluating as true and performing the calculation designated even when it should be false.

For instance, if Att_Year = 2015 then the test should be true, but if Att_Year = 2016 it should be false ( dayofYear 2/29/16 is 60 and dayofyear 3/1/16 is 61)

If anyone has any ideas why this is happening, it would be extremely helpful.

As an aside, I've inserted the test into DataViewer and switched between a 2016 record and 2015 record, DataViewer evaluates correctly.

Thanks,

Jason

• 1. Re: Calculation Evaluates as True Always

Middle(Evaluate(date(2;29;yourYear)); 4; 1) = 7 will be 1 if the year is bisextile and 0 if not.

• 2. Re: Calculation Evaluates as True Always

Dude...kudos for using "bisextile" correctly in a sentence.

• 3. Re: Calculation Evaluates as True Always

It would appear this calculation evaluates False always...

I learned a new word though!

-Jason

• 4. Re: Calculation Evaluates as True Always

Leap year - Wikipedia, the free encyclopedia

http://science.howstuffworks.com/science-vs-myth/everyday-myths/question50.htm

Middle(Evaluate(date(2; 29; 1900)); 4; 1) = 7 gives 0

Middle(Evaluate(date(2; 29; 2000)); 4; 1) = 7 gives 1

• 5. Re: Calculation Evaluates as True Always

I was able to get the original test to work by making Att_Year Extend ( Att_Year ).

Thank you Siplus for the help, I never knew that test for leap years and will keep it in mind in the future.

-Jason