Calculation doesn't work

[Guest]

Okay, I'm still having trouble. I thought this would work:

Status 10 Time: IIf ([Rec’d Back]Is Null, (DateDiff("d",[Returned],Date())), (DateDiff(“dâ€,[Returned],[Rec’d Back])))

Can you not use an IIf expression this way? I'm trying to figure the age one set of cases whose "aging" has been interrupted by further investigation (day-clock stops), other cases that have been interrupted and then returned (they have a gap).

The above equation figures the stopped cases nicely, but it also figures the cases with a gap the same way, i.e, the returned date is the same as today's date even if there's an earlier returned date entered.

 

[Ted Allen]

Try using:

iif(IsNull([Yourfield]),....)

HTH,

Ted Allen

-----Original Message-----
Okay, I'm still having trouble. I thought this would work:

Status 10 Time: IIf ([Recâ?Td Back]Is Null, (DateDiff

("d",[Returned],Date())), (DateDiff(â?odâ?,[Returned],
[Recâ?Td Back])))

Can you not use an IIf expression this way? I'm trying

to figure the age one set of cases whose "aging" has been
interrupted by further investigation (day-clock stops),
other cases that have been interrupted and then returned
(they have a gap).

The above equation figures the stopped cases nicely, but

it also figures the cases with a gap the same way, i.e,
the returned date is the same as today's date even if
there's an earlier returned date entered.

 

[Ted Allen]

No, you should definitely be able to use the iif function
to specify two different expressions. The problem with
your initial iif condition was that you were putting if
the field is null. You have to use the IsNull() function
to check to see if a field is null. The truepart and
falsepart do not have to be strings. They can be
expressions, nested functions, etc.

Post back with the query sql if you can't get the iif()
function to work.

-Ted Allen

-----Original Message-----
I think the problem is you can't use an IIf expression

to return two different calculations. They have to be
string expressions.

I'm currently trying to create a UNION subquery to see

if that will work, but I have no idea what I'm doing.

What I essentially need is for the Returned date to

default to the current date if the field is null.

Try using:

iif(IsNull([Yourfield]),....)

HTH,

Ted Allen

-----Original Message-----
Okay, I'm still having trouble. I thought this would work:

Status 10 Time: IIf ([Recâ?Td Back]Is Null, (DateDiff

("d",[Returned],Date())), (DateDiff(â?odâ?Â, [Returned],
[Recâ?Td Back])))

Can you not use an IIf expression this way? I'm

trying
to figure the age one set of cases whose "aging" has been
interrupted by further investigation (day-clock stops),
other cases that have been interrupted and then returned
(they have a gap).

The above equation figures the stopped cases nicely,

but
it also figures the cases with a gap the same way, i.e,
the returned date is the same as today's date even if
there's an earlier returned date entered.

.

.