Calculating days between dates

[KevinE]

Hello,

I have a simple query set up that returns all customer orders. I was
wondering if it's possible to add a caculated field that would return the
number of days between consecutive dates/orders.

This is the sort of thing that I have in mind......
30/10/05
04/01/06 66
22/01/06 18
04/02/06 13
25/02/06 21
18/03/06 21

Thanks in advance for any help,
Kevin

 

[Tom Ellison]

Dear Kevin:

For this I consistently use a correlated subquery:

SELECT YDate,
(SELECT DateDiff("d", Nz(MAX(T1.YDate), T.YDate), T.YDate)
FROM YourTable T1
WHERE T1.YDate < T.YDate)
AS Interval
FROM YourTable T
ORDER BY YDate

In the above, replace YourTable with the name of your table or query, and
TDate with the name of your date column.

I have put a 0 in the date difference column for the first row.

Consider what you want it to do if you have two identical dates, unless you
have absolutely prevented that from ever happening. You may not have sample
data that tests this, but if it is ever going to be possible, you had best
think about it now.

Tom Ellison