Validation Rule

  • Thread starter Thread starter Somecallmejosh
  • Start date Start date
S

Somecallmejosh

I have a form that contains the following fields...
Date1Received
Date1Returned
Date2Received
Date2Returned
Date3Received
Date3Returned
DateApproved

I have a validation rule on all fields that states they
must be greater than the previous field. Date1Returned
through Date3Returned are not required fields, but may
contain dates. I would like to set up a conditional rule
for the DateApproved field that states it must be greater
than Date1Received, Date2Received(If Not Null), and
Date3Received (If Not Null). Can this be accomplished as
a validation rule, or would it require code? Any advice
suggestions, and/or examples would be greatly appreicated.

Sincerely,
Josh
 
That's way too complicated for a table level Validation rule. The following
Untested, Air Code should work in the BeforeUpdate event of your form:

If Date3Received Is Not Null OR Date2Received Is Not NullThen

If DateApproved < Date3Received AND DateApproved < Date2Received AND
DateApproved < DateReceived Then

MsgBox "Approval Date Must be greater than the All the Received Dates"

Cancel = True

End If

End If



As I said, this is untested, so you will probably need to do a few tweaks to
it, but it should get you started.
 
Lynn,

Thank you very much for your help. I've set up the code
you recommended in the Before Update on the form. I am
getting a compile error when attempting to open the
form. In the debugger it points to the second line of
code. Here's what I have written..
----------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Date3Rec Is Not Null Or Date2Rec Is Not Null Then

If DateApproved < Date3Rec And DateApproved < Date2Rec
And DateApproved < Date1Rec Then

MsgBox "Approval Date Must be greater than All the
Received Dates"

Cancel = True

End If

End If
End Sub

---------------------------------------------------------
I've changed the word "integer" to "date" in the second
line, but that doesn't seem to help. Is there something
I could be overlooking? Thanks for your help.


Sincerely,
Josh
 
The error is probably coming from lines wrapping. Make sure each line is
complete or, else use the line continuation mark, the underscore character.

Put "Integer" back in the parm list for the BeforeUpdate event. It has to be
that way.
 
Back
Top