creating a "no double bookings" routine... (re post as am now a member

[AKAC00L]

Hi,

I'm building an access database for a property rental scheme.

Basically what I have is a list of properties and a list of clients. I want to book the clients into the properties on various days but do not want to double book, obviously if each client only stayed for 1 day I wouldn't have a problem as I could use the no dupes thing on the design view of the tables, but as the clients will be staying for different lenghts of time (3 days, 8 days etc.) this won't work. so basically I need a column for start date, then a column for end date then something to check and see anywhere between those dates has been booked before on previous records for that particular property...

Any help / suggestions would be gratefully appreciated.

Thanks.

 

[RickSpr]

For each record for the given property, the following must be true: Either the new booking start date is > the record's end date, or the new booking end date is < the record's start date. If both of those conditions are false in any record, then the new booking overlaps the booking represented by that record.

As a query, you'd express this as &quot;WHERE StartDate <= #new booking end date# AND EndDate >= #new booking start date#&quot;. If the query returns any records, then there is at least an overlap.

You may need to change that to &quot;<&quot; and/or &quot;>&quot; if you allow a booking to start on the same day as another one ends.

You may want to do a forum search for additional ideas. I remember a very similar question, also about property rentals, came up sometime last spring or summer. Rick Sprague

 

[AKAC00L]

sounds good, so how do I put that in a query?

 

[RickSpr]

To put it into an Access query object, one way is to switch to SQL view and type it at the end of a SQL SELECT statement, turning the italicized parts into query parameters.

To put it into a SQL query statement in code, you build the query statement in a string by concatenating string literals containing the non-italicized part with the variables (formatted as dates using Format$).

I really can't be more specific unless you provide more specific information about your application. But I suspect you haven't designed it yet. If you're a novice and don't know how to design your application, you'll need to study Access for a while first, especially looking at the sample application (NorthWind) and learning how and why it works. Rick Sprague