Date Difference Calculation Between two Records

[Vinay]

If somebody can help me , regarding calculating difference of days between to
records.

Ex:

Record No: Date: Receipts Payments
123 01/04/2007 10000
456 10/04/2007 5000
789 20/05/2007 500

What i need is calculate date difference in two records

Record No: Diff
456 9 Days
789 40 Days

I need this days for interest calculation
if somebody could please suggest how to calculate this date difference in
query
i will be very much grateful for this solution.

Vinay.

 

[John Spencer]

You can use as subquery in a DateDiff Calculation

SELECT [Record No],
DateDiff("d",
NZ((SELECT Max([Date])
FROM YourTable as Tmp
WHERE Tmp.[Date] < YourTable.[Date]),YourTable.Date),YourTable.Date)
as Elapsed
FROM YourTable

That should return three records
123 : 0
456 : 9
789 : 40


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

If somebody can help me , regarding calculating difference of days between to
records.

Ex:

Record No: Date: Receipts Payments
123 01/04/2007 10000
456 10/04/2007 5000
789 20/05/2007 500

What i need is calculate date difference in two records

Record No: Diff
456 9 Days
789 40 Days

I need this days for interest calculation

You can use this kind of expression in a textbox:
=DateDiff("d", DMax("[Date]", "yourtable", "[Date] < " &
Format([Date], "\#yyyy\-m\-d\#"), [Date])

If you will need to sum those values, you may be better
using that as a calculated field in the report's record
source query.