IIf function to calculate field

[mikelee101]

Hello,
I'm running into an issue and was hoping someone could help. I have a
query that I'd like to calculate the time between two dates. There
are 3 date fields involved: Received Date, Tasks Complete Date and
Closed Date. If Closed Date exists, then it should calculate Closed
Date - Received Date. If Closed Date is null and Tasks Complete Date
exists, then it should calculate Tasks Complete Date - Received Date.
If both Closed Date and Tasks Complete Date are null, then it should
return "N/A."

Here's what I've got and what it's doing:

Turn Time: IIf(IsNull([tblTransferData]![Closed
Date]),IIf(IsNull([tblTransferData]![Tasks Complete Date]),"N/A",
[tblTransferData]![Tasks Complete Date]-[tblTransferData]![Received
Date]),[tblTransferData]![Closed Date]-[tblTransferData]![Received
Date])

If Closed date is not null, it's calculating correctly.
If Closed date is null and Tasks Complete Date is not null, it's
calculating correctly.
If Closed date and Tasks Complete Date are both null, it's returning
#Error.

If I replace the false instructions in the inner IIf function with a
text expression, it no longer returns an error. Example:

Turn Time: IIf(IsNull([tblTransferData]![Closed
Date]),IIf(IsNull([tblTransferData]![Tasks Complete Date]),"N/A","N/
B"),[tblTransferData]![Closed Date]-[tblTransferData]![Received Date])

If Closed date is not null, it's calculating correctly.
If Closed date is null and Tasks Complete Date is not null, it's
returning N/B (as expected).
If Closed date and Tasks Complete Date are both null, it's returning N/
A (as expected).

I'm at a loss. Is there a limit to the number of calculations that
you can do in a single expression? Or did I make an obvious bracket
v. parenthesis error that I just can't see because I've been staring
at this thing so long?

Access 2000, XP Pro SP2

Thanks to all.

Mike Lee
Coppell, TX, USA

 

[RoLaAus]

I don't know if this would make any difference or not, but try the
datediff function

IIf(IsNull([tblTransferData]![Closed
Date]),IIf(IsNull([tblTransferData]![Tasks Complete Date]),"N/
A",DateDiff("d",[tblTransferData]![Tasks Complete Date],
[tblTransferData]![Received Date])),[tblTransferData]![Closed Date]-
[tblTransferData]![Received Date])

 

[Duane Hookom]

I think it would be much easier to create a small user-defined function that
accepts three variants for the dates and returns a variant. I would never
retun a string value since you really want to always return a number or null.
You can use the format property of a text box to display "N/A" for null
values.

 

[John Spencer]

Turn Time: IIf([tblTransferData]![ClosedDate] Is Null AND
[tblTransferData]![Tasks Complete Date] is Null,"N/A",
NZ([tblTransferData]![Closed Date],[tblTransferData]![Tasks Complete
Date])-[tblTransferData]![ReceivedDate])


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mikelee101]

Turn Time: IIf([tblTransferData]![ClosedDate] Is Null AND
[tblTransferData]![Tasks Complete Date] is Null,"N/A",
NZ([tblTransferData]![Closed Date],[tblTransferData]![Tasks Complete
Date])-[tblTransferData]![ReceivedDate])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello,
I'm running into an issue and was hoping someone could help.  I have a
query that I'd like to calculate the time between two dates.  There
are 3 date fields involved:  Received Date, Tasks Complete Date and
Closed Date.  If Closed Date exists, then it should calculate Closed
Date - Received Date.  If Closed Date is null and Tasks Complete Date
exists, then it should calculate Tasks Complete Date - Received Date.
If both Closed Date and Tasks Complete Date are null, then it should
return "N/A."

Here's what I've got and what it's doing:

Turn Time: IIf(IsNull([tblTransferData]![Closed
Date]),IIf(IsNull([tblTransferData]![Tasks Complete Date]),"N/A",
[tblTransferData]![Tasks Complete Date]-[tblTransferData]![Received
Date]),[tblTransferData]![Closed Date]-[tblTransferData]![Received
Date])

If Closed date is not null, it's calculating correctly.
If Closed date is null and Tasks Complete Date is not null, it's
calculating correctly.
If Closed date and Tasks Complete Date are both null, it's returning
#Error.

If I replace the false instructions in the inner IIf function with a
text expression, it no longer returns an error.  Example:

Turn Time: IIf(IsNull([tblTransferData]![Closed
Date]),IIf(IsNull([tblTransferData]![Tasks Complete Date]),"N/A","N/
B"),[tblTransferData]![Closed Date]-[tblTransferData]![Received Date])

If Closed date is not null, it's calculating correctly.
If Closed date is null and Tasks Complete Date is not null, it's
returning N/B (as expected).
If Closed date and Tasks Complete Date are both null, it's returning N/
A (as expected).

I'm at a loss.  Is there a limit to the number of calculations that
you can do in a single expression?  Or did I make an obvious bracket
v. parenthesis error that I just can't see because I've been staring
at this thing so long?

Access 2000, XP Pro SP2

Thanks to all.

Mike Lee
Coppell, TX, USA- Hide quoted text -

- Show quoted text -

John,
Worked like a charm...thanks! I'm still a little confused at the
results I got from the nested IIfs, but as long as there's a
workaround, I'm happy.

Thanks again.

Mike