datediff

[Jeanette]

Is there some way to use datediff to calculate how much time has passed between two dates in one field. I have a closingdate field that can have more than one closing date for a project. I need to how much time has passed between the first closingdate and the last closingdate. Again, a project can have many closingdates. I hope this is not too confusing. Thanks in advance.

 

[IanH]

It would be useful to know the structure of your tables but, as you said that the dates were in the same field, I would suggest you create two queries.

Query 1 would group on the project ID, maximum of start date and minimum of startdate

Query 2 would calculate the date difference.

e.g.

SELECT ProjectID, MAX(closingdate) AS Max_close, MIN(closingdate) AS Min_close
FROM DataTable
GROUP BY ProjectID;

(Save this as Query1)

Query 2 could look like ...

SELECT ProjectID, DATEDIFF("d", Min_close, Max_close) AS Days_diff
FROM Query1;

 

[IanH]

Had a further thought about this, and you can of course do it in one query if you use the domain functions DMax and DMin.

The query would look a bit like this ....


SELECT ProjectID, DateDiff("d",DMin("[closingdate]","Table1","[ProjectID] = " & [Table1]![ProjectID]),DMax("[closingdate]","Table1","[ProjectID] = " & [Table1]![ProjectID])) AS Difference
FROM Table1
GROUP BY ProjectID;


assuming that you have a datatable called Table1, and fields ProjectID and closingdate

Regards

ian

 

[Jon K]

Or this:

SELECT ProjectID, DateDiff("d",Min(closingdate), Max(closingdate)) AS [Number Of Days]
FROM Table1
GROUP BY ProjectID;

 

[IanH]

OK so it was late, I was tired ...