Autocalc problem for a field accessing another record

[Greg Hains]

Hi.

Im having a problem getting my head around something. I have a table with two (relevant) fields - one a date, and the other a duration. The table is sorted descending by the date field, and the duration is supposed to contain the difference in days from the date of that record, and the date of the previous record.

eg.

15/3/08 6

9/3/08 4

5/3/08 2

3/3/08 0

Note that as the first record (sorted) cannot compare to a pevious date so it's value is zero - no problem there.

Whats actually happening is that I have used a loop to start from the first record and move through to the last inserting into the field the date subtract previous-records-date - which I think is clumsy - only because when it gets to the final record it calculates incorrectly - my fault I know.

Can anybody please point me in the right direction for an auto-enter calculation for this field whereby it automatically calculates the value of duration based on it's own date minus the next lower date in the list? Im not sure how to access other record data in the calculation stage.

Failing this, can somebody help with a loop/script that would not fail on the first and last records.

Any help would be great.

Greg

[Raybaudi]

...the duration is supposed to contain the difference in days from the date of that record, and the date of the [color:red]previous record.

eg.

15/3/08 6

9/3/08 4

5/3/08 2

3/3/08 0

Note that as the [color:red]first record (sorted) cannot compare to a pevious date so it's value is [color:red]zero

Hi

based on what you said, I think that the result must be:

15/3/08 0

9/3/08 6

5/3/08 4

3/3/08 2

BTW: try the attached file.

DurationByFoundSet.zip

[Greg Hains]

Hi Daniele,

Thankyou for your reply - and your English is just fine.

The formula you have provided works just fine for what I want to do.

Cheers,

Greg

[Raybaudi]

Hi Greg

Thank you.

BTW: Remember that unstored calcs are not a good thing for performance. Maybe it's better to use that type of calculation into a script.