VALIDATION RULE PROBLEM

[BOONEY]

I have a table where a "StartDate" and "EndDate" field is entered for each record. I would like to ensure that the "EndDate" entered is always greater that the "StartDate". Both fields are in a general date format.

Thank you.

 

[scriverb]

This would have to be done with VBA code in form where the data is being entered. Are you using a form to enter these records? The code can check both fields prior to updating the table and if the condition values are not appropriate then the cursor can be returned to the field in error with a message to fix it.

In the Before Update of the form put the following:
If Me.BeginDate > Me.EndDate then
MsgBox "The Begin Date must be before the End Date"
Me.BeginDate.setfocus
DoCmd.CancelEvent
end if

The update will not occur with this code and the cursor will be returned to the Begin Date control. I did not blank out the date information in either control so that the user could analyze the data entered and realize where the error occurred which could be either the Begin Date or End Date.

Let me know if you need further assistance with this.


Bob Scriver

 

[BOONEY]

Thank you for your response. However, what I am after is to ensure that the end date is greater than the begin date. The begin date is entered first, and then the end date.

 

[scriverb]

I realize that. This code get pasted into the Forms Before Update event procedure. After the fields are entered and just before the record is saved either by a click of a button or moving to the next record the code will be run and the fields analyzed. By checking the Begin Date compared to the End Date we are also checking the End Date and its relationship to the Begin Date. You can also put in in the After update of the End Date but if this record were edited and the user changed just the Begin Date then that code would not run.

Bob Scriver

 

[BOONEY]

Thank you very much Bob.