Limiting date entry

[Andy Spicer]

I have a database that keeps tract of the hours put on our jobs, but when an incorrect date gets entered in (usualy the year), it screws up are ability to tract the hours properly. How can I give the date field a two week +/- limit on each entry and use a pop up window to alert the user of an incorrect date entry?

 

[Jack Cowley]

In the Before Update event of the date field code similar to this should work:

If DateDiff("ww", Date, [DateField]) <= -2 Or DateDiff("ww", Date, [DateField]) >= 2 Then
MsgBox "Your date is wrong."
Cancel = True
End if

 

[joeyreyma]

you can also make more strict entry by applying a validation rule in your table.

eg.

>=DateAdd("ww",-2,Date()) And <=DateAdd("ww",2,Date())

but then again, you should be careful because you will have hard time changing old values since they will be limited to +/- 2 weeks of the current date eventhough they occur past this range.

 

[Andy Spicer]

I tried to put that code in the Before Update event and it wouldn't work for me. I ended up putting this into the Validation event;Between DateAdd("d",-14,Date()) And DateAdd("d",14,Date())

Thank you for the help.