Validation rule - data must be 'not' be between two dates

[Flint]

Validation rule - data must 'not' be between two dates

I have a data entry form based on a table where you can book in dates for jobs. One of the fields is called 'JobDate' and another 'StaffID' (which is related to tblStaff)

I have another table called tblStaff which has fields StaffID, HolidaysStart, HolidaysEnd

I want a validation rule that does not accept a record that has a Job Date entered for a Staff that is between HolidaysStart and HolidaysEnd.

The problem is HolidaysStart and HolidaysEnd are not on the same form as Job Date, so is it possible to do this by putting a validation rule in the control for the JobDate field?

 

[theprez]

Make the user select the Staffid first. Make the staffid field a combo box (cmbStaffID) that has its rowsource based on tblStaff. The combobox rowsource can have 3 fields where HolidayStart and HolidayEnd are not displayed. Do this by making the columnwidths = 2;0;0 for the combobox

After the user selects the staffid, they would then select a jobdate. In the afterupdate event for the jobdate, you can run your edit check

if jobdate >= me.cmbstaffid.column(1) and jobdate <= me.cmbstaffid.column(2) then (do whatever you need to do, set a warning, undo the date, etc...)

When referring to columns in a combo box, column(0) refers to the first field (StaffID).