Multiple IIf statements 3

[Eugenios]

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

 

[jrbarnett]

Something like:

Iif(Not IsNull([Depot Out]),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]),"")

Basically you have another Iif statement in the place of the query for a null [Depot Out] field.

John

 

[Eugenios]

DepotFee: IIf(Not IsNull([Depot Out Date]),
DatePart("d",[Depot In Date])*[Storage Fee/Day],
IIf(DateDiff("d",[Depot In Date],
Date())<31,DateDiff("d",[Depot Out Date],Date())*[Storage Fee/Day],
31*[Storage Fee/Day]))

Solved i guess thanks

 

[PHV]

From the OP, I'd use this.
DepotFee: [Storage Fee/Day]*IIf([Depot Out Date] Is Null,IIf(DateDiff("d",[Depot In Date],Date())<31,DateDiff("d",[Depot In Date],Date()),31),DateDiff("d",[Depot In Date],[Depot Out Date]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Do yourself a favor and avoid nested iifs.
1) As you can see they are unreadable and confusing
2) They do not short circuit
3) they are not very reusable
4) they are notoriously slow
5) can not perform additional error checking

Build your own function something like this and pass the field names in as a parameter. You then can use this anywhere: calculated field, code, or query.

Public Function getStorageFee(feePerDay As Single, depotIn As Variant, depotOut As Variant)
  If IsNull(feePerDay) Or Not IsDate(depotIn) Then
    exitFunction
  ElseIf IsDate(depotOut) Then
    getStorageFee = feePerDay * DateDiff("d", depotIn, depotOut)
  ElseIf DateDiff("d", depotIn, Date) < 31 Then
    getStorageFee = feePerDay * DateDiff("d", depotIn, Date)
  Else
    getStorageFee = feePerDay * 31
  End If
End Function

A heck of a lot easier to read and error check.

 

[Eugenios]

thanks, going to work on this