Nested If Expressions

[Lynn]

Hi,
I have an unbounded control on my form and I want to have a nested if expression attached to it.

I have three radio buttons: Approved, Denied and Accepted. I also have two fields: DateOfCommunication and Timeframe.

Here's what I came up with for a nested statement. Suffice it to say that it doesn't work...



=IIF([Approved] = True Or [Accepted] = True, (IIF([Timeframe] = "6 months", DateAdd("m",6,[DateOfCommunication]),DateAdd("m", 12, [DateOfCommunication])), Null)

So in effect I'm wanting the expression to figure out a date in the control if Approved or Accepted is clicked and no value in the control if Denied is clicked.

Am I even close?
Lynn

[maximus]

This is What I infer from your post.

You Have three options:
1) Approved
2) Accepted
2) Denied

You have a Field Timeframe and DateOfCommunication

Now here is what you want to do:
1) When Approved is Checked: Resultant Date will be a date 6 months from the Date of Communication.
2) When Accepted is Checked: Resultant Date will be a date 12 months from the Date of Communication.
3) When Denied is selected: Resultant Date is Null.

I also assume these are not multiselection options which means user will not be required to check two options at and point.

My solution is not independent check box but an option group the code used as follows:
Private Sub frmOption_AfterUpdate()
Select Case Me.frmOption
Case Is = 1
Me.Text11 = DateAdd("m", 6, Me.Text9)
Case Is = 2
Me.Text11 = DateAdd("m", 12, Me.Text9)
Case Is = 3
Me.Text11 = vbNullString
End Select
End Sub

Attached is an mdb. Please refer to the same.

Mark this thread solved if this solves your problem.

[Lynn]

Hi,
That's a great help, thank you. Just to clarify though:
1) When Approved or Accepted is checked, the resulting date will depend on whether "6 months" or "12 months" is keyed into the Timeframe field. So I'd input as follows:

Click Approved or Accepted, then enter in [Timeframe] either "6 months" or "12 months" and the resulting date would calculate.

2) When Denied is checked, the resulting date field is null, as you have it above. so:

Click Denied, resulting date field would be blank whether I inputted something in [Timeframe] or not.

Thanks,
Lynn

[maximus]

Same setup but enhanced the code a little bit.
Scope of the Code:

1) Check if Date of Communication is Blank if yes you will get a prompt.
2) Check if Time Frame is blank if yes you will get a prompt.
3) Verifies whether Time frame is either 6 or 12 if not you will get an error prompt.
4) if Denied is Selected clears result date and time frame.
5) if contditions are not met the option group remains unselected i.e. if you click Approved and conditions are not meet the option is not selected this means that after filling in valid data into the fields you can straight away select Approved you don't have to select some other option and then select approved.

Code:


Private Sub frmOption_AfterUpdate()
Select Case Me.frmOption
Case Is = 1
If IsNull(Me.Text9) Then
MsgBox "Please Type Date of Communication", vbInformation, "Type Date of Communication"
Me.frmOption = 0
ElseIf IsNull(Me.Text13) Then
MsgBox "Please type a time frame", vbInformation, "Time Frame not Entered"
Me.frmOption = 0
ElseIf Me.Text13 <> 6 And Me.Text13 <> 12 Then
MsgBox "Time frame has to be either 6 months or 12 months", vbInformation, "Invalid Time Frame"
Me.frmOption = 0
Else
Me.Text11 = DateAdd("m", Me.Text13, Me.Text9)
End If
Case Is = 2
If IsNull(Me.Text9) Then
MsgBox "Please Type Date of Communication", vbInformation, "Type Date of Communication"
Me.frmOption = 0
ElseIf IsNull(Me.Text13) Then
MsgBox "Please type a time frame", vbInformation, "Time Frame not Entered"
Me.frmOption = 0
ElseIf Me.Text13 <> 6 And Me.Text13 <> 12 Then
MsgBox "Time frame has to be either 6 months or 12 months", vbInformation, "Invalid Time Frame"
Me.frmOption = 0
Else
Me.Text11 = DateAdd("m", Me.Text13, Me.Text9)
End If
Case Is = 3
Me.Text11 = vbNullString
Me.Text13 = Null
End Select
End Sub


if this solves your problem mark the thread solved.

[Lynn]

It would have taken me forever to figure this out. Thanks so much!
Lynn

[maximus]

I am glad this code helped you happy accessing