Calculating days between dates of different records

[cscs]

Trying to calculate the days between the date field of different records.

Example:

Field1 Field2
1 11/12/02
2 11/08/02
3 11/06/02
4 11/03/02

Result I want...

Field1 Field2 Result
1 11/12/02 4
2 11/08/02 2
3 11/06/02 3
4 11/03/02 0

How could I accomplish this in an SQL statement.

Thanks.

 

[dodge20]

DateDiff

What function does: returns a variant to reflect the time difference between two dates

Syntax: DateDiff( inteval, firstdate, seconddate [,firstweekday[ [ , firstweek])

Example

The following shods the difference in days between two dates:

= DateDiff("d", "11-05-2002", "11-07-2002&quot returns 2



Dodge20

 

[Golom]

Probably something like

Select A.Field1, A.Field2, nz(datediff("d", B.Field2, A.Field2),0) As [Diff]
From DateFields As A LEFT JOIN DateFields As B ON A.Field1 = B.Field1 - 1

 

[PaulBricker]

This may do it if Golom's doesn't.

SELECT x.ID, x.Date, nz(datediff("d",x.Date,y.Date) AS Expr1
FROM TableName AS x, TableName AS y
WHERE y.ID=[x].[id]-1;

Paul