Date Diff Formulas

[Jessica]

I currently have a datediff formula that calculates lenght of time in care by
comparing DOE field with current date. I Need the formula to automatically
stop counting the months when i enter a date under the closed date field. I
hope i'm being clear.

 

[Douglas J. Steele]

Presumably you've currently got something like

DateDiff("d", [OpenDate], Date())

Change that to

DateDiff("d", [OpenDate], Nz([ClosedDate], Date()))

 

[Pat Keller]

Assuming 'date closed' is an empty or null field it should be something like:

TimeInCare: iif(DateClosed is
Null,datediff(DOE,date()),datediff(DOE,DateClosed))

Without knowing your specifics the syntax will need modification, but that's
thegeneral idea.... check for DateClosed and use that if it exists, otherwise
use the current date.

 

[Jessica]

Pat,

I enter the formula and is not working.

Assuming 'date closed' is an empty or null field it should be something like:

TimeInCare: iif(DateClosed is
Null,datediff(DOE,date()),datediff(DOE,DateClosed))

Without knowing your specifics the syntax will need modification, but that's
thegeneral idea.... check for DateClosed and use that if it exists, otherwise
use the current date.

 

[Pat Keller]

You don't say where you're trying to do this. Are you in Basic, trying to
create a query field? My syntax is query-based and it still depends on the
specific field names you are using. The syntax is different depending on
where you're at. Also - I did not know datediff was a built in function -
you're already using it so I assume you know the syntax. Doug's post is
probably more syntactically accurate for your needs.

 

[John Spencer]

Missed arguments in the DateDiff calls. "D" = Days or "M" = months

TimeInCare: iif(DateClosed is
Null,datediff("d",DOE,date()),datediff("d",DOE,DateClosed))

You should know that DateDiff counts the transitions from one date to
another. So you will get a count of 1 if the dates are Jan 31 2008 and
Feb 1 2008. That is you transitioned from Jan to Feb.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jessica]

Yours also workes

Presumably you've currently got something like

DateDiff("d", [OpenDate], Date())

Change that to

DateDiff("d", [OpenDate], Nz([ClosedDate], Date()))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I currently have a datediff formula that calculates lenght of time in care
by
comparing DOE field with current date. I Need the formula to automatically
stop counting the months when i enter a date under the closed date field.
I
hope i'm being clear.

 

[Jessica]

Thank you now it works

Missed arguments in the DateDiff calls. "D" = Days or "M" = months

TimeInCare: iif(DateClosed is
Null,datediff("d",DOE,date()),datediff("d",DOE,DateClosed))

You should know that DateDiff counts the transitions from one date to
another. So you will get a count of 1 if the dates are Jan 31 2008 and
Feb 1 2008. That is you transitioned from Jan to Feb.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================