Nested IIf in Calculated Field

[raweber]

Helpful friends,

I have a calculated field in a form that checks to see if the completion date is filled in. If not, it returns the CURRENT date minus the start date. If it is, it returns the COMPLETION date minus the start date.

Here is my (working) code for that:

=IIf(IsNull([CompletionDate]),DateDiff("d",[DateReceived],Date()),DateDiff("d",[DateReceived],[CompletionDate]))

Now I want to added the ability to suspend the study by entering Suspend and Resume dates. My new IIf statement includes several nested IIf's to account for the three possible conditions: Suspend Date, Resume Date and Completion Date. Here is the code I'm trying to use for this:

=IIf(IsNull([CompletionDate]),IIf(IsNull([SuspendDate),DateDiff("d",[DateReceived],Date()),IIf(IsNull([ResumeDate]),DateDiff("d",[DateReceived],[SuspendDate]),DateDiff("d",[DateReceived],Date())-DateDiff("d",[SuspendDate],[ResumeDate]))),IIf(IsNull([SuspendDate),DateDiff("d",[DateReceived],[CompletionDate]),IIf(IsNull([ResumeDate]),”Need Resumption Date”,DateDiff("d",[DateReceived],[CompletionDate])-DateDiff("d",[SuspendDate],[ResumeDate]))))

As you can see, a heck of a lot more complicated.

The problem is that when I try to save it, I get an error that the expression has "A function containing the wrong number of arguments."

I've gone through the code several times and everything seems to be in order. Each IIf has a Then condition and an Else condition. Everything is separated by commas and all the parentheses line up properly. (If anyone is really curious, I can e-mail you the Word file where I broke things up by line and colored coded things to try and find the error.)

One thing I wondered - this statement has 446 characters - is there a limit?

If this won't work as conceived, is there a better way to attack this? Maybe in VBA with an On Current or On Load routine?

Thanks, Rob

 

[plog]

VBA.

Create a function to handle all the cases instead of trying to cram it all the comparisons into one line. Its possible to do, but its a lot easier when you aren't working with a one line limitation.

 

[raweber]

All right - great! I've got that working. I want to use this value in reports, as well, but I'll go onto the reports forum to ask that.