MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Date Difference Calculation Between two Records

• Follow

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

```
 0

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

```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]
```
 0

2 Replies
790 Views

Similiar Articles:

7/24/2012 11:47:12 AM