Date Validation

[matthewnsarah07]

I have got a table tblLeaveRequest which has 3 date fields in; [DateofRequest], [DateFrom] & [DateTo] which are used on frmLeaveRequest

I want to add some validation rules to these fields:

[DateofRequest] - needs to restricted to dates no older than 10 days prior to system current date and no dates in the future at all

[DateFrom] - if possible to able to enter a date that is more than 12 months into the future

[DateTo] - cannot be before DateFrom field

I have been reliably informed that this too complex at table level

Thanks for your help as always

 

[missinglinq]

[DateFrom] - if possible to able to enter a date that is more than 12 months into the future

This is pretty standard validation stuff, but you've got to explain the above statement better than you have so far!

 

[matthewnsarah07]

Apologies

It should read:

[DateFrom] user not able to enter a date that is more than 12 months in to the future based on system date

Thanks

 

[missinglinq]

This validation code will fulfill your needs as posted:

Private Sub DateOfRequest_BeforeUpdate(Cancel As Integer)
 If (DateDiff("d", DateOfRequest, Date) > 10) Or DateOfRequest > Date Then
   MsgBox "Request Date Must Be No Greater Than 10 Days Prior To Today and No Greater Than Today"
   Cancel = True
 End If
End Sub

Private Sub DateFrom_BeforeUpdate(Cancel As Integer)
If DateDiff("d", Date, DateFrom) > 365 Then
  MsgBox "'Date From' Cannot Be Greater Than One Year In The Future"
  Cancel = True
End If
End Sub

Private Sub DateTo_BeforeUpdate(Cancel As Integer)
 If DateTo < DateFrom Then
   MsgBox "'Date To' Cannot Be Prior to 'Date From'"
   Cancel = True
 End If
End Sub

 

[matthewnsarah07]

Thanks for all your help

As always its very much appreciated