How do i prevent overlapping dates in a microsoft access form.
Posted on 2004-10-01
I have a simple Access database called Time. There are three fields in the table, the first field is number which is the primary key - this is an autonumber, and then start time and end time for the second and third fields which use date/time.
I have a form for data entry which is connected to the Time table. On this form i would like a validation rule which will prevent me from entering an overlapping time on the form. Below is an example of an overlapping time.
1 9.00 - 10.00am
2 8.30 - 9.30pm
I have written an SQL query which will detect overlapping times but i cannot apply this query as a constraint, i am also not very good with VBA or VB so if a solutionis given in Vb make it clear and concise.
SELECT DISTINCTROW Time.Number, Time.[Start Time], Time.[End Time]
FROM [Time], [Time] AS Time_1
WHERE (((Time.Number)<>Time_1.Number) And ((Time_1.[Start Time]) Between Time.[Start Time] And Time.[End Time])) Or (((Time.Number)<>Time_1.Number) And ((Time_1.[End Time]) Between Time.[Start Time] And Time.[End Time]));