not let date >30 days or < 30 days from today

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know I put this in the validation rule for the date field in the table but
I am having a hard time getting the right syntax. The field name is service
date - I do not want the user in the form to be able to put a date in less
than or greater than 30 days from today.

Thanks,
Barb
 
Use this as the validation rule:

Between DateAdd("d",-5,Date()) And DateAdd("d",5,Date())
 
I do not want the user in the form to be able to put a date in less
than or greater than 30 days from today.
Click to expand...

I'd suggest not using a validation rule to do this - if the record is valid
today, a validation rule would make it invalid a month from now!

Instead, use the BeforeUpdate event of the textbox or date picker control:

Private Sub txtEventDate_BeforeUpdate(Cancel as Integer)
If Me!txtEventDate < DateAdd("d", -30, Date) _
OR Me!txtEventDate > DateAdd("d", 30, Date) Then
MsgBox "Please enter a date within thirty days", vbOKOnly
Cancel = True
Me!txtEventDate.Undo
End If
End Sub

John W. Vinson [MVP]
 
Thanks it worked great!!!

John W. Vinson said:
I'd suggest not using a validation rule to do this - if the record is valid
today, a validation rule would make it invalid a month from now!

Instead, use the BeforeUpdate event of the textbox or date picker control:

Private Sub txtEventDate_BeforeUpdate(Cancel as Integer)
If Me!txtEventDate < DateAdd("d", -30, Date) _
OR Me!txtEventDate > DateAdd("d", 30, Date) Then
MsgBox "Please enter a date within thirty days", vbOKOnly
Cancel = True
Me!txtEventDate.Undo
End If
End Sub

John W. Vinson [MVP]
Click to expand...
 
Back
Top