Mulitple IIF Statements (Query and/or VBA solution)

[dee]

I need to calculate the number of days based on several variables. I need it to be able to output the conclusion (the number of days) in the query along with the other data for export or I need some other solution so that the entire query can be exported along with this output. The name of the field is [X] (already exists).

IF [X] Is Not Null, leave current value.
IF [X] Is Null go through the following scenarios in order to determine the number of days:
If [STATUS]=1, then input 0 or leave blank.
If [STATUS]=17, then input 0 or leave blank.
If [STATUS]=16 AND [DATE1] Is Not Null, then calculate [DATE1] – [DATE2].
If [STATUS]=16 AND [DATE1] Is Null, then input 0 or leave blank.


If [STATUS]=19, then input 0 or leave blank.
If [STATUS]=10, then input 0 or leave blank.
If [DATE3] Is Null AND [DATE1] Is Not Null, then subtract [DATE2] from [DATE1]
If [DATE3] Is Not Null AND [DATE1] Is Not Null, then subtract [DATE3] from [DATE1]
If [DATE3] is Not Null AND [DATE1] Is Null, then subtract the [LAST DAY OF THE LAST QUARTER] from [DATE3]
If [DATE4] Is Not Null AND [DATE3] is Null AND [DATE1] Is Null, then subtract the [DATE2] from [DATE4].
If [DATE4] Is Not Null AND [DATE3] is Not Null AND [DATE1] Is Null, then input 0 or leave blank.
Those separate dates signify the date that certain actions were taken and we need to know the number of days it takes to get from point A to point B. Getting the number of days it takes to get from point A to point B depends on all of those variables mentioned in the post. All of those scenarios have to be taken into account.

[pbaldy]

This is an instance where I would create a public function. An nested IIf() would be hard to build/maintain. Here's a start if you need it:

http://www.baldyweb.com/Function.htm

In your case the various values referred to above would be input parameters.