Validation rule to prevent bookings being made at the same time

[mudassir92]

Hi. I am creating a booking system for a cleaner (project) on Access, and I need to prevent double bookings being made at the same time on the same date. I have a 'Job Date' field which is the day the cleaner has to do the job and a 'Job Time' field which is a drop down list of times with half hour gaps in between e.g. 7:00 am, 7:30 am, 8:30 am etc.
I have done some research and I know how to prevent double bookings if there is a start and end time for the job, but in my case it is not. Help is appreciated!
Thanks

[Rabbit]

If there's no end time, how would you know if something is double booked?

[mudassir92]

For example I don't want two bookings being made on 18/03/2012 at 9:30 am

[NeoPa]

Typically, this is done using Time Interval Overlapping (MS Access 2003).

If you have periods with no end however, you need to consider what you're really asking (preferably before posting your question). If you want to consider a period without an end point as terminating in the now, then treat empty (Null) values as Now(). If you'd like to treat them as ending never, then #12/31/9999# makes a good approximation to that.

I'm afraid we're in no position to tell you what your question should have been though.

[mudassir92]

i see what you mean with #12/31/9999#
the bookings last half an hour each, so #12/31/9999# is the way to go about it right?
Thanks

[NeoPa]

The main point I was trying to make is that we cannot possibly answer that, as that is for you to decide.

Certainly, if you want to treat a record without an end date as indicating one that goes on for ever, then that would work for you.

Nz([End Date Field], #31/12/9999#)