Verify If Start Date Exists In Another Record Before Write

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Garrison - 17 Jul 2007 14:06 GMT

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

Reply to this Message

Allen Browne - 17 Jul 2007 14:23 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello all...
>
[quoted text clipped - 17 lines]
>
> Jeff G

Reply to this Message