GET DATEDIFF FUNCTION TO RETURN "0"

[Guest]

I have an unbound control named: DateResolvedStepB with a control source:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())) previously
provided to me by John Vinson. It works fine until I get records that do not
have data entered in the field DateResolvedStepB because the dispute was
resolved at an earlier step than Step B. If such an occurrence happens I want
the value for this control to be 0 because I have a prior control that will
give me the result for that question. However, if there still has not been a
resolution at Step B I want the control to:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())).

1. Is there a solution using the DateDiff function? If so, please help.
2. If not, where would I put code to give me 0 and what would the code be?

Thanks for the help.

 

[pietlinden]

I have an unbound control named: DateResolvedStepB with a control source:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())) previously
provided to me by John Vinson. It works fine until I get records that do not
have data entered in the field DateResolvedStepB because the dispute was
resolved at an earlier step than Step B. If such an occurrence happens I want
the value for this control to be 0 because I have a prior control that will
give me the result for that question. However, if there still has not been a
resolution at Step B I want the control to:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())).

1. Is there a solution using the DateDiff function? If so, please help.
2. If not, where would I put code to give me 0 and what would the code be?

Thanks for the help.

Sounds like you're trying to fight the way DateDiff works. Why not
just use an IIF() statement

IIF(IsNull(DateResolvedStepB), 0, DateDiff("d",[IncidentDate],Now()))

 

[Douglas J. Steele]

I have an unbound control named: DateResolvedStepB with a control source:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())) previously
provided to me by John Vinson. It works fine until I get records that do
not
have data entered in the field DateResolvedStepB because the dispute was
resolved at an earlier step than Step B. If such an occurrence happens I
want
the value for this control to be 0 because I have a prior control that
will
give me the result for that question. However, if there still has not
been a
resolution at Step B I want the control to:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())).

1. Is there a solution using the DateDiff function? If so, please help.
2. If not, where would I put code to give me 0 and what would the code
be?

Thanks for the help.

Sounds like you're trying to fight the way DateDiff works. Why not
just use an IIF() statement

IIF(IsNull(DateResolvedStepB), 0, DateDiff("d",[IncidentDate],Now()))

I suspect he may want

IIF(IsNull([DateResolvedStepB]), 0,
DateDiff("d",[IncidentDate],[DateResolvedStepB]))

The following should work as well

DateDiff("d",[IncidentDate],nz([DateResolvedStepB],[IncidentDate])).

And just a comment: if all that's of interest is days, there's no point
using the Now() function: use Date().

 

[Guest]

Pietlinden/Doug:

Thanks for the help.

I used:
=IIf(IsNull([DateResolvedStepB]),0,DateDiff("d",[IncidentDate],nz([DateResolvedStepB],[IncidentDate]))).

I still do not understand when I should use ( and [. It's been 35 years
since I did any programing on the old PDP 8's & 9's and IBM 360's using
punched cards. I was going to say in the old'n days (1968) the number of (
had to be an even number. After counting the ( the total is an even number,
8. Oh well, never mind!
--
Bob

I have an unbound control named: DateResolvedStepB with a control source:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())) previously
provided to me by John Vinson. It works fine until I get records that do
not
have data entered in the field DateResolvedStepB because the dispute was
resolved at an earlier step than Step B. If such an occurrence happens I
want
the value for this control to be 0 because I have a prior control that
will
give me the result for that question. However, if there still has not
been a
resolution at Step B I want the control to:
=DateDiff("d",[IncidentDate],nz([DateResolvedStepB],Now())).

1. Is there a solution using the DateDiff function? If so, please help.
2. If not, where would I put code to give me 0 and what would the code
be?

Thanks for the help.

Sounds like you're trying to fight the way DateDiff works. Why not
just use an IIF() statement

IIF(IsNull(DateResolvedStepB), 0, DateDiff("d",[IncidentDate],Now()))

I suspect he may want

IIF(IsNull([DateResolvedStepB]), 0,
DateDiff("d",[IncidentDate],[DateResolvedStepB]))

The following should work as well

DateDiff("d",[IncidentDate],nz([DateResolvedStepB],[IncidentDate])).

And just a comment: if all that's of interest is days, there's no point
using the Now() function: use Date().

 

[John W. Vinson]

I still do not understand when I should use ( and [.

Square brackets delimit the names of Objects (such as forms or controls) and
Collections (such as [Forms], the collection of all open forms). Parentheses
delimit the arguments of functions, or arithmetic expressions which need to be
treated as a unit; they're also needed in some SQL contexts.

It's been 35 years
since I did any programing on the old PDP 8's & 9's and IBM 360's using
punched cards. I was going to say in the old'n days (1968) the number of (
had to be an even number. After counting the ( the total is an even number,
8. Oh well, never mind!

Well, I just count ( as +1, ) as -1, and start looking for the mistake if the
sum is nonzero. There's certainly no requirement that the number of left
parens be even; most often there'll just be one of each.

John W. Vinson [MVP]