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??
--
pcover

On Thu, 22 Oct 2009 09:13:02 -0700, pcover <(E-Mail Removed)>
wrote:

>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??

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
--

John W. Vinson [MVP]

Try this to use current date if not closed.
DaysOpen: (DateDiff("d",CDate([...DATE_CREAT]),CDate(Nz([...DATE_CLS],
Date()))))

--
Build a little, test a little.


"pcover" wrote:

> 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??
> --
> pcover

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


"pcover" wrote:

> 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??
> --
> pcover

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"pcover" wrote:

> 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??
> --
> pcover

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.



"pcover" wrote:

> 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??
> --
> pcover

On Thu, 22 Oct 2009 09:59:01 -0700, Daniel Pineault
<(E-Mail Removed)> wrote:

>Why are you using CDate on your fields? Are they not dates?

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.
--

John W. Vinson [MVP]

On Thu, 22 Oct 2009 17:43:42 GMT, "KenSheridan via AccessMonster.com"
<u51882@uwe> wrote:

>You've done exactly the same as I did at first, John, and missed the extra
>closing parenthesis.

The difference between the master carpenter and the journeyman...

>Otherwise, what's that they say about 'great minds and
>fools'?

"Great minds run in the same channels; little minds run in the same
gutters"...
--

John W. Vinson [MVP]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On Thu, 22 Oct 2009 09:59:01 -0700, Daniel Pineault
><(E-Mail Removed)> wrote:
>
>>Why are you using CDate on your fields? Are they not dates?
>
> 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.

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 23 Oct 2009 00:37:06 GMT, "David W. Fenton" <(E-Mail Removed)>
wrote:

>John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
>news:(E-Mail Removed):
>
>> On Thu, 22 Oct 2009 09:59:01 -0700, Daniel Pineault
>><(E-Mail Removed)> wrote:
>>
>>>Why are you using CDate on your fields? Are they not dates?
>>
>> 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.
>
>But that won't be relevant if you're applying it directly to a field
>of type Date/Time.

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).
--

John W. Vinson [MVP]