Expressions in Forms: linking IIF statements

[keithstg]

Hello All,
I have created a form for user entry, that will measure a set of information against a business day deadline. However, there are two potential business day deadlines -3 and 5. I was able to create a formula that correctly captured the five day deadline:

[=IIf(IsNull([arrival date]),"In Progress",IIf(IsNull([completion date]),"In Progress",IIf([arrival date]=[completion date],"Deadline Obtained",IIf(DateDiff("d",[arrival date],[completion date])<=5,"Deadline Obtained",IIf(DateDiff("d",[arrival date],[completion date])>5,"Deadline Missed","false")))))]

However, I needed to add functionality to determine the number of business days required to open an account via a new field containing a formula that matches account types to the number of business days required to open the account. I am having difficulty setting up the formula to take into account whether the account is subject to a three or five business day deadline - I have the following formula written:

IIf([business days requested]=5,IIf(IsNull([date all mandatory information received]),”In Progress”,IIf(IsNull([completion date]),”In Progress”,IIf([date all mandatory information received]=[completion date],”Deadline Obtained”,IIf(DateDiff(“d”,[date all mandatory information received],[completion date])<=5,”Deadline Obtained”,IIf(DateDiff(“d”,[date all mandatory information received],[completion date])>5,”Deadline Missed”,IIf([business days requested]=3,IIf(IsNull([date all mandatory information received]),”In Progress”,IIf(IsNull([completion date]),”In Progress”,IIf([date all mandatory information received]=[completion date],”Deadline Obtained”,IIf(DateDiff(“d”,[date all mandatory information received],[completion date])<=3,”Deadline Obtained”,IIf(DateDiff(“d”,[date all mandatory information received],[completion date])>3,”Deadline Missed”, "false))))))))))))

I am continually getting an "operand without operator" error, or a syntax error with this formula. Am I unable to create a formula based on a calculated field in a form, or am I unable to link two IIF strings?
Any / all opinions are appreciated! Thank You.

 

[KenHigg]

You need to re-format your post so that it's easier to read. (Please )

 

[Kiwiman]

Howzit

Check the end of the formula - it shows "false)))...

should it be "false"))... ?

this may be the issue?

 

[keithstg]

Sorry - I didn't paste the entire formula - the missing " is my issue. Double checked the formula in the form and it has a complete set of "'s. Any other thoughts? I think that perhaps having the IIf[business days requested]=5 is the issue...

 

[Kiwiman]

Howzit

You could be right - a little difficult to interpret. But what happens when the business days requested does not = 5? From waht I see there is no expression for the false part of the iif statement - basically nothing happens

try at the end of your iif stmt

...))),[b]"Something Else"[/b])

 

[keithstg]

Thanks! I will give that a shot. Ideally, the business days requested, if not =5, would be =3, and the second IIF string would be used. I separated the two possible days (3 and 5), but if the code you proposed doesn't work, how would I structure the formula to account for a business days requested = either 3 or 5, and the formulas progressing through there?

Sorry for the follow-up questions!

 

[Kiwiman]

Howzit

Something like this should work - it may not be the most optimum solution but...

I would call the sub FLDComp in the after Update event of the 3 controls.

You will need to rename my control references to your control names.

I would also contemplate renaming your fields in your tables to exclude spaces if possible.

Private Sub FldComp()
   
    ' Check if Business Days are 3 or 5
    If Me.BusDaysReq <> 3 And Me.BusDaysReq <> 5 Then
        Me.Progress = "Not a valid business day"
    ' Check if either of the date fields are null or ""
    ElseIf IsNull(Me.DtMandInfo) Or Me.DtMandInfo = "" Or IsNull(Me.DtComp) Or Me.DtComp = "" Then
        Me.Progress = "In Progress"
    Else
        ' Work out the progress status
        Deadline
    End If
    
'Me.Refresh
'Debug.Print Me.BusDaysReq
 'Debug.Print Me.Progress
 

End Sub

Private Sub Deadline()

' Work out the status of the line based on business day requested
Select Case Me.BusDaysReq
    ' Business Days is = 3
    Case Is = 3
        ' Based on Days diff determine the status
        Select Case DateDiff("d", Me.DtMandInfo, Me.DtComp)
            Case Is <= 3
                Me.Progress = "Deadline Obtained"
            Case Else
                Me.Progress = "Deadline Missed"
        End Select
    
    ' Business Days is = 5
    Case Is = 5
    ' Based on Days diff determine the status
        Select Case DateDiff("d", Me.DtMandInfo, Me.DtComp)
            Case Is <= 5
                Me.Progress = "Deadline Obtained"
            Case Else
                Me.Progress = "Deadline Missed"
        End Select
    ' if not 3 or 5, then insert some other value, Not rerally needed as the iif stmt above takes care of it
    Case Else
        Me.Progress = "Not a valid business day"
    
End Select
   

End Sub