Multiple Date Validation

[Guest]

I need to validate multiple dates, and I'm not sure whether to do this in a
table or a form, or how to code it.

invoicedate needs to be <= today.
invoicereceived needs to be >= invoicedate and <= today.
dateapproved needs to be >= invoicereceived and <= today.
datesubmitted needs to be >= dateapproved and <= today.

(et cetera...)

How can I make this happen?

Any suggestions are appreciated.

Thank you.

-Michelle

 

[Guest]

Hi, JerseyGirl.

This is most easily done using a form's BeforeUpdate event, which occurs
after changes have been made to a record when the user attempts to move off
of the record, and before the changes have been saved to the table.
Something like:

Dim ctl As Control
Dim blnValid As Boolean

' Initialize flag
blnValid = True

' Loop through each control. If data is not valid, set blnValid to False.
For Each ctl In Me.Controls
Select Case ctl.Name
Case "InvoiceDate"
If Not ctl.Value <= Date Then
blnValid = False
End If
Case "InvoiceReceived"
If Not (ctl.Value >= Me!InvoiceDate And ctl.Value <= Date)
Then
blnValid = False
End If
Case "DateApproved"
If Not (ctl.Value >= Me!InvoiceReceived And ctl.Value <=
Date) Then
blnValid = False
End If
Case "DateSubmitted"
If Not (ctl.Value >= Me!DateApproved And ctl.Value <= Date)
Then
blnValid = False
End If

End Select

If blnValid = False Then
' Cancel the record save, set focus to offending field, and exit
the For loop
MsgBox ctl.Name & " is not valid. Please reenter."
ctl.SetFocus
Cancel = True
Exit For
End If

Next ctl

Hope that helps.
Sprinks