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.

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
'====================================================


Vinay wrote:
> 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.
> 

Vinay wrote:

>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.

-- 
Marsh
MVP [MS Access]