If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Checking date availability

Hi all, hope you can help me:

Basically what im trying to do is a database which checks to see if something is available.

The database is a booking system for services. I need the database to check a "booking" table to see if there is a booking on a specific date. If there is, then you cannot make a booking. If there isn't then you can.

In my "booking table" there is a list of dates of bookings. Each booking lasts a week. So if someone wants to make a booking, it cant just check to see if the first day is available, it needs to check for 6 additional days. (i.e. a booking on 01 Jan 2006). If someone wants to make a booking, the database needs to account for a whole week, so the next available booking would be on 07 Jan 2006).

There is probably another way, but this is probably the easiest. I am assuming you mean that there will always be 7days between booking and could never be for example: a booking from 1/1-1/6,1/2-1/7, no over-lap only 1/1-1/6, 1/7-1/12, weekends are not an issue, etc. This will give you the next available date.

First, create a query pulling in your booking date field only from your table. Click on the Totals button and the toolbar. Where the field is listed, go to Total and select MAX from the drop down. This will give you the last date entered as a book date. Save this query. Create another query in design view pulling in the query you just created. You do not need to bring your date filed down to the bottom. You book date should now show as maxofbook date in this query. Create and expression to calculate your next available date:
NextAvailDte: =dateadd("D",6,[maxofbook date])
This will take your last day booked and add six days to that date giving you the next available date. From there you can create a button in your form or something to show you the date open before you make an entry. However, if you booking are not always back to back, this is not going to work because this method will only look at the last date entered. So if someone wanted 1/1 then the next date available was 1/7 but the next one you booked wasn't until 1/15, it will only look from 1/15.
Hope this makes sense. Maybe it can steer you in the right direction anyway.