Date Differences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
Wondering if anyone knows how to do a date difference calc from a table that
looks like the following:
Date PurchaseName
28/01/2005 John
21/01/2005 John
07/01/2005 John
04/01/2005 John

To calculate a datedifference field like this:

Date PurchaseName DaysDifference

28/01/2005 John 7
21/01/2005 John 14
07/01/2005 John 3
04/01/2005 John 0

Any help greatly appreciated.Tks
Mark
 
****Untested****
SELECT Curr.PurchaseDate, Curr.PurchaseName,
DateDiff("d", IIf (IsNull(Prev.PurchaseDate), Curr.PurchaseDate,
Prev.PurchaseDate),
Curr.PurchaseDate),
FROM YourTable AS Curr, YourTable AS Prev
WHERE Prev.PurchaseDate IN
(
SELECT Max(Sub.PurchaseDate)
FROM YourTable AS Sub
WHERE (Sub.PurchaseName = Curr.PurchaseName)
AND (Sub.PurchaseDate < Curr.PurchaseDate)
)
********

There may be other more efficient SQL Strings.

"Date" is a bad Field name since Date is also a reserved word (for the VBA
function Date()). Suggest you change the Field name (I used "PurchaseDate"
in the above SQL String)
 
Back
Top