creating a "no double bookings" routine...

[Guest_imported]

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.

 

[Frink]

Hallo,

Can you use a query to check a given day is not between any of the start/end dates in the database?

- Frink

 

[AKAC00L]

sounds good, but can give a little more info please?

 

[AKAC00L]

Also please don't forget that I have more than one property so dates can overlap, just not for the same property...

 

[Frink]

Hallo,

If a client wanted to book PropertyID 27 for 01/01/1002
then you could run a query:

SELECT * FROM tblProperty WHERE lngPropertyID=27 AND 01/01/2002 between datStartDate AND datEndDate

This probably won't work, as comparing dates is a bit strange, you can use '#' characters or format the dates, or whatever is appropriate, but you get the basic idea:
If it returns no records, then that day is free for that property.
Obviously there are lots of other ways to do this, it depends on what you want to do, where you want to call it from etc.
If you let me know as much detail as you can, I can suggest further things, if you like.

- Frink

 

[AKAC00L]

Thanks Frink,

The problem with that is that I need to check availability for eg 1 week at a time.

the situation is this:

I have 7 apartments available for rent 365 days a year.
each apartment can be rented out from anywhere between 1 and the full 365 days, although realisticly its normally for a period of 1 or 2 weeks, but there are enough exceptions to warrant having to do this.

Basically I want something where you can choose which apartment you want (dropdown menu from another table) enter the start date and the number of days, which will automatically work out the end date (know how to do that) but will also tell you if anywhere during that time the apartment is booked. Don't want to use the validation thing as sometimes it's ok to double book eg when one client is leaving in the morning and the next client arriving same day in the evening.

also ideally I would like to create a report which shows eacg apartment, all 365 days of year and colours in the days where the apartment is already booked, for a quick look up. I had imagined it something like this:

APT ADDRESS
MONTH
---------------------
1|2|3|4|5|6| etc.
---------------------

with the dates that it is booked coloured in?

any help would be grately apreciated.

Thanks.

 

[AKAC00L]

I'm also ona bit of a tight deadline getting this done, so would really apreciate a reply as I'm already paying the rent for the office that I'm basing the business in.

Thanks.