Date Diff

[ryan1]

I have a many query that records dates. I'm trying to find the difference in days between a date to the next date which are in decending order(there are 20 or so dates). Any Suggestions?

 

[PaulBricker]

You can try this. Change your Table name for Table1 and your date field for DateField. Table1_1 is the result of adding Table1 to the query twice so just change the Table1 part and leave the _1 when you change table names.

SELECT Table1.DateField, Max(Table1_1.DateField) AS MaxOfDateField, Table1DateField - MaxOfDateField As DifferenceInDays
FROM Table1, Table1 AS Table1_1
WHERE (((Table1.DateField)<Table1_1.DateField))
GROUP BY Table1.DateField
ORDER BY Table1.DateField DESC;

Try it and post back with problems. Because we don't have other fields to group on, I'm not sure how just date fields will preform in this situation.

Paul