Calculate Date Difference in Y, M, D format

[Palacelaw]

I want to calculate the difference between Opened_Date and Date_Completed in
Y, M, D format in a field called Number_Of_Open_Days with the following
conditions.

If Opened_Date is null then Number_Of_Open_Days should be null. If
Date_Completed is null then Number_Of_Open_Days should be the difference in
Y, M, D format between Today and Opened_Date.

I've tried various combinations of DateDiff with no luck.

Thanks in advance for your help.

 

[Marshall Barton]

I want to calculate the difference between Opened_Date and Date_Completed in
Y, M, D format in a field called Number_Of_Open_Days with the following
conditions.

If Opened_Date is null then Number_Of_Open_Days should be null. If
Date_Completed is null then Number_Of_Open_Days should be the difference in
Y, M, D format between Today and Opened_Date.

Try using:
DateDiff("d", Nz(Date_Completed, Date()), Opened_Date)

I don't understand what you mean by
"the difference in Y, M, D format"

 

[Douglas J. Steele]

Take a look at http://www.accessmvp.com/DJSteele/Diff2Dates.html

If IsNull(Me.Opened_Date) Then
Me.Number_Of_Open_Days = Null
Else
Me.Number_Of_Open_Days = Diff2Dates("ymd", CDate(Me.Opened_Date),
Date())
End If

 

[Drew]

Try using:
DateDiff("d", Nz(Date_Completed, Date()), Opened_Date)

I don't understand what you mean by
"the difference in Y, M, D format"

To answer your question - for example if Opened Date is 1/1/2008 and Date
Completed is 2/2/2009 then Number of Open Days would be 1 year, 1 month, 1
day.

 

[Marshall Barton]

To answer your question - for example if Opened Date is 1/1/2008 and Date
Completed is 2/2/2009 then Number of Open Days would be 1 year, 1 month, 1
day.

Ahhh, then use Doug's fancy function. Make sure that you
use Nz(Date_Completed, Date()) to deal with Null completion
date.