If statement question

L

Lori

I am trying to write a rule that says if Application Type is New or Renewal
then Due Date is required. I'm not sure how to write this, what I have
doesn't work:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Application_Type = 'New' or 'Renewal' and
IsNull(Me.Application_Due_Date) = True Then
MsgBox "Please enter the Application Due Date."
Cancel = True
End If

Any help is greatly appreciated! Thanks
 
A

Allen Browne

The field needs to be repeated for the 2 values.
The brackets are important when you mix AND and OR.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.Application_Type = "New"' Or Me.Application_Type = "Renewal") _
And IsNull(Me.Application_Due_Date) Then
MsgBox "Please enter the Application Due Date."
Cancel = True
End If
End Sub

An even easier solution would be to open the table in design view, open the
Properties box (View menu), and add a validation rule for the table:
([Application_Due_Date] Is Not Null) OR ([Application_Type] Is Null) OR
([Application_Type] <> "New" AND [Application_Type] <> "Renewal")
 
S

SusanV

You have to put Me.Application_Type = for both conditions:

If Me.Application_Type = 'New' or Me.Application_Type = 'Renewal' and
IsNull(Me.Application_Due_Date) = True Then
MsgBox "Please enter the Application Due Date."
Cancel = True
End If
 
L

Lori

Thanks for your help- it worked! I tried to apply the same type of code you
provided to another similar situation and for some reason it didn't work. I
put this under the recent post of "Count question". Any help is much
appreciated. BTW - you have responded to many questions I have had over the
past months and have been very helpful. Thanks so much! Lori

Allen Browne said:
The field needs to be repeated for the 2 values.
The brackets are important when you mix AND and OR.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.Application_Type = "New"' Or Me.Application_Type = "Renewal") _
And IsNull(Me.Application_Due_Date) Then
MsgBox "Please enter the Application Due Date."
Cancel = True
End If
End Sub

An even easier solution would be to open the table in design view, open the
Properties box (View menu), and add a validation rule for the table:
([Application_Due_Date] Is Not Null) OR ([Application_Type] Is Null) OR
([Application_Type] <> "New" AND [Application_Type] <> "Renewal")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lori said:
I am trying to write a rule that says if Application Type is New or Renewal
then Due Date is required. I'm not sure how to write this, what I have
doesn't work:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Application_Type = 'New' or 'Renewal' and
IsNull(Me.Application_Due_Date) = True Then
MsgBox "Please enter the Application Due Date."
Cancel = True
End If

Any help is greatly appreciated! Thanks
Click to expand...
Click to expand...
 
Top