DateDiff Error Calculating Time if Date Closed is Blank

  • Thread starter Thread starter pcover
  • Start date Start date
P

pcover

I am using the following formula to calulate the number days between creation
date & closing date.

DaysOpen: (DateDiff("d",CDate([...DATE_CREAT]),CDate([...DATE_CLS])))

It works well as long as the both dates are complete. However, there are
times when some items have not yet been closed so the field is blank. In
this case it returns the following error "#Error" I need to get rid of this
error message and replace it with 0.

Any ideas??
 
I am using the following formula to calulate the number days between creation
date & closing date.

DaysOpen: (DateDiff("d",CDate([...DATE_CREAT]),CDate([...DATE_CLS])))

It works well as long as the both dates are complete. However, there are
times when some items have not yet been closed so the field is blank. In
this case it returns the following error "#Error" I need to get rid of this
error message and replace it with 0.

Any ideas??
Click to expand...

Try

DaysOpen: (DateDiff("d",CDate([...DATE_CREAT]),CDate(NZ([...DATE_CLS],
[...DATE_CREAT])))

That is, use the NZ (Null To Zero) function to return the DATE_CLS if it
exists, and return instead the DATE_CREAT value if DATE_CLS is null
 
Try this to use current date if not closed.
DaysOpen: (DateDiff("d",CDate([...DATE_CREAT]),CDate(Nz([...DATE_CLS],
Date()))))
 
pcover,

Try this:
DaysOpen: IIf(Not
IsNull([YourTableName]![CloseDate]),DateDiff("d",[YourTableName]![CreationtDate],[YourTableName]![CloseDate]),0)

Substitue "YourTableName" with the actual name of your table, "CloseDate"
with the name your your field representing the Close Date and "CreationtDate"
with the name of your filed representing the Creation Date.

Watch out for line wrap here. This should be all one line.
-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 
DaysOpen: IIf(IsNull([...DATE_CREAT] + [...DATE_CLS]) = True, 0,
DateDiff("d",CDate([...DATE_CREAT]),CDate([...DATE_CLS])))

CDate can't handle nulls. Hopefully the IIf above will catch the nulls first
and return the 0 before the fields are evaluated with CDate.
 
Try something like:

DaysOpen: IIF(IsDate(CDate([...DATE_CREAT]))=True AND
IsDate(CDate([...DATE_CLS]))=True,DateDiff("d",CDate([...DATE_CREAT]),CDate([...DATE_CLS])),"")

Why are you using CDate on your fields? Are they not dates?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Why are you using CDate on your fields? Are they not dates?
Click to expand...

Probably from my suggestion - I've got users who apply some... imagination...
to inputting dates, and have found that CDate() can coerce the user's input
into a date that Access can recognize. Sometimes.
 
You've done exactly the same as I did at first, John, and missed the extra
closing parenthesis.
Click to expand...

The difference between the master carpenter and the journeyman...
Otherwise, what's that they say about 'great minds and
fools'?
Click to expand...

"Great minds run in the same channels; little minds run in the same
gutters"...
 
Probably from my suggestion - I've got users who apply some...
imagination... to inputting dates, and have found that CDate() can
coerce the user's input into a date that Access can recognize.
Sometimes.
Click to expand...

But that won't be relevant if you're applying it directly to a field
of type Date/Time.
 
But that won't be relevant if you're applying it directly to a field
of type Date/Time.
Click to expand...

The intent (which may have been lost in translation) is that I was intending
that it be applied to an unbound Textbox on a form, containing a user-entered
criterion (which might or might not be in appropriate date format).
 
The intent (which may have been lost in translation) is that I was
intending that it be applied to an unbound Textbox on a form,
containing a user-entered criterion (which might or might not be
in appropriate date format).
Click to expand...

I understood that, but in the present circumstance, it got applied
improperly to the table fields. I was actually surprised it didn't
throw an error, as so many of the type conversion functions croak
when you pass them the data type they are supposed to return.
 
Back
Top