Subtracting Dates

  • Thread starter Thread starter Jay Yate
  • Start date Start date
J

Jay Yate

I have a report that needs to subtract dates in the same
column different row e.g. date from row 1 subtracted from
date on row 2 etc.

The query orders by date DESC so all dates are less than
the prior date.

Subtracting 2 dates in 2 different columns of the same row
is easy. I'm not sure how to approach this.

Thanks.
 
Jay said:
I have a report that needs to subtract dates in the same
column different row e.g. date from row 1 subtracted from
date on row 2 etc.

The query orders by date DESC so all dates are less than
the prior date.
Click to expand...


Use a calculated field in the report's record source query.
the general idea is to use a subquery something like this:

SELECT thetable.somefields, thetable.datefield,
(SELECT Max(X.datefield)
FROM thetable AS X
WHERE X.datefield < thetable.datefield
) AS PrevDate
FROM thetable
 
Back
Top