Verify If Start Date Exists In Another Record Before Write

[Jeff Garrison]

Hello all...

I trying to do some validation of a record before it writes to be database.
Here's the scenario:

My table includes the following fields:

contractID Autonumber Field
contractContractor The Contractor's ID
contractProjectNumber The Project Number
contractStartDate The Start Date of the Project
contractEndDate The End Date of the Project


I have the entry done on a form with those fields. What I want to do is
when the next record is entered, BEFORE the record is written to the
database, check to see if the new record falls within any previous Start or
End Dates.

Any help would be much appreciated.

Jeff G

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form to perform the validation.

Use DLookup() to see if an overlapping entry exists in the table.

Assuming contractStartDate and contractEndDate are required fields (so you
don't have to handle overlapping dates when one of the fields is blank), the
dates overlap if:
A starts before B ends, AND
B starts before A ends.
and presumably it's the same contractor and/or project number as well.

So, the Criteria for your DLookup() will contain several phrases. Don't
forget ot exclude the contractID (i.e. an existing record does not clash
with itself.)

It may help to draw example events on paper like this:
A: StartDate-----------EndDate
B: StartDate-----------EndDate
C: StartDate------EndDate
to get the idea of how they overlap.