I have a field called accInterestEndDate with the following calculation:

Let (

[

~nextEntry = GetNthRecord ( date ; Get ( RecordNumber ) + 1 )

] ;

Case (

IsEmpty ( ~nextEntry ) ;

portfolioDate ;

Min ( ~nextEntry ; portfolioDate )

)

)

In the first record, date equals 01.01.2011. In the second record, date equals 02.02.2011. portfolioDate is a global field that equals 06.11.12.

With the above calculation, accInterestEndDate in the first record should be 02.02.2011. However, it calculates to 01.01.2011 ( its own date field).

I can fix this by changing the variable ~nextEntry to GetAsDate ( GetNthRecord ( date ; Get ( RecordNumber ) + 1 ) ). Then the correct result of 02.02.2011 appears. However, I don't understand why this fixes the problem and I don't like implementing a solution without understanding it.

Now where it gets really strange. I added a field calleddateNextRecord which has the calculation GetNthRecord ( date ; Get ( RecordNumber ) + 1 ), without wrapping it in GetAsDate(). Then, I have the field accInterestEndDate with the following calculation:

Case (

IsEmpty ( dateNextRecord ) ;

portfolioDate ;

Min ( dateNextRecord ; portfolioDate )

)

Strangely enough, this calculates correctly.

My understanding of the GetAsDate() function is that it will convert text to a date. However as the date field in the portfolio date field are already formatted as date fields, I don't understand why this corrects the error. Any input would be helpful.

Please see attached file.

DateCalc.fp7.zip

There is a bug with GetNthRecord(). See here:

http://fmforums.com/...-join-or-union/

and

http://fmforums.com/forum/topic/78008-get-related-via-getnth/

It's too bad this guy didn't come back. I thought he had a lot of guts.

Edited June 12, 201213 yr by LaRetta