Date/Time Validation

[FLINTSTONE]

Currently the Access Database (its a bookings system) includes "Date", "TimeStart" and "TimeFinish" fields. When a record is added, these need filling in

Is there a validation rule so that prevents anyone from double booking on the same date AND time on any given day/time?


For example if one record already has 'time start' as 14:00 and 'time finish' as 16:00, 'Date' as 19/03/04, when a new record is being added it should not accept it if you have 19/03/04 as "Date" with a "TimeStart or "TimeFinish" between 14:00 to 16:00.
It should accept it if its on the same date but outside this range, or in this range but on a different date

Also how do you make it so that the time entered in "TimeFinish" must be later than the time entered in "TimeStart" or else it won't be accepted?

Also how do you make it so that the database will only allow weekdays dates to be entered in the "Date" field and not accept any dates that are on a Saturday or Sunday?

 

[bat17]

To validate data you can use the before update event of the control. For the time that is a simple [TimeFinish]>[TimeStart] comparison.
To check the day you can use the Weekday(date, [firstdayofweek]) function.

To validate between two periods will take a bit more thought than I have time for at the moment

With your example would it be OK if the start time was before the existing entry but overlaps it whith the finish time? i.e. start at 13:00 and finish at 15:00 or 17:00?

Peter

 

[FLINTSTONE]

Thanks for the help the [Timefinish]>[timestart] is now working

With your example would it be OK if the start time was before the existing entry but overlaps it whith the finish time? i.e. start at 13:00 and finish at 15:00 or 17:00?

The finish time has to be before the start time of an exisiting entry so it can't overlap

 

[FLINTSTONE]

Does anyone know any other Access forums which could help with this problem?


The system should only allow one time period to be booked

E.g.
If one booking has "Date" 19/03/04 "TimeStart" 15:00 "TimeFinish" 16:00 then the next booking on this date MUST have a "TimeStart and TimeFinish after 16:00 or before 15:00.