Multiple IIf statements

[Eugene Marzins]

Hello everyone. I'm trying to create a formula that will return values based on several fields. I have 2 dates - Depot In and Depot Out. The formula requires more than 2 calculations and this is where get confused whether it is possible to use a IIf statement.

The formula is used to calculate the storage fees of containers

The logic behind the formula is - If [Depot Out] IsNotNull then datediff("d",[Depot In],[Depot Out])*[Storage Fee] ... now here's the trouble: the statement divides into 2 more ifs - if [Depot Out] IsNull and datediff("d",[Depot In],Date())<31 then datediff("d",[Depot In],Date())*[Storage Fee] otherwise 31*[Storage Fee]

Any ideas?

Best regards,Eugene

 

[Lord Kelvan]

iif([Depot Out] IsNotNull, datediff("d",[Depot In],[Depot
Out])*[Storage Fee],iif([Depot Out] IsNull and datediff("d",[Depot
In],Date())<31,datediff("d",[Depot In],Date())*[Storage Fee],
31*[Storage Fee]))

if i get you right try that ... you can have nested iif in access just
keep tagging them on in the commas i cannot remember how many levels
you can have yo to but i think it is 10 nested statements

hope this helps if it isnt please explain it more clearly

regards
kelvan

 

[Marshall Barton]

Hello everyone. I'm trying to create a formula that will
return values based on several fields.
I have 2 dates - Depot In and Depot Out.
The formula requires more than 2 calculations and this
is where get confused whether it is possible to use a IIf statement.

The formula is used to calculate the storage fees of containers

The logic behind the formula is -
If [Depot Out] IsNotNull then datediff("d",[Depot In],[Depot Out])*[Storage Fee]
... now here's the trouble: the statement divides into 2 more ifs -
if [Depot Out] IsNull and datediff("d",[Depot In],Date())<31 then
datediff("d",[Depot In],Date())*[Storage Fee] otherwise 31*[Storage Fee]

When concocting nested IIf functions, it usually helps if
you first write it out on multiple lines with balanced
indenting. I think your expression would look like:

IIf([Depot Out] Is Not Null,
datediff("d", [Depot In], [Depot Out]) * [Storage Fee],
IIf(datediff("d", [Depot In], Date()) < 31.
datediff("d", [Depot In], Date()) * [Storage Fee],
31 * [Storage Fee]
)
)

Note: there is no need to test [Depot Out] for Null in the
second IIf because it's in the else part of the first IIf