Trying to "Check Availability" somehow ????

[toaster045]

Hey guys I wonder if you can help me. I'm not sure if this is a form/macro or query issue so can post elsewhere if need though.

I'm currently an A level student and tweaking this prototype before making the real thing. Ok I’m building a bookings database for a hotel and trying to make the various queries to generate reports, etc.
but I’ve come up with the idea of making a "Check Availability" function so that when entering data into one of the form this can be hit which will run the macro which will run this.
Ok so I’ve got these fields:
Booking No - Number - Long Integer
Room no - Number - Long Integer
Customer No - Number - Long Integer
Date of Check in - Date/Time - Short Date

Date of Departure - Date/Time - Short Date

I've got more but I’m guessing these are the ones I need in the query. Anyway I need to make a query that will somehow - allow the user to input dates in the form - then check to see if those values already exist and return "No not available" or "Yes they are".
So yeah sorry for the long post I’m new to this whole advance query making and I’m useless at SQL so Design view tips would be handy. I'm using Access 2007 and greatly appreciate anyone’s thoughts. If you need anymore info pls ask.

Thanks
Toby

 

[pbaldy]

The trick to this type of query is that you want to compare the start date on your form to the end date in the data, and the end date on the form to the start date in the data.

 

[toaster045]

The trick to this type of query is that you want to compare the start date on your form to the end date in the data, and the end date on the form to the start date in the data.

Thank you for coming back to me So this is actually possible then (that's nice to know ). But how do I go about comparing the data?
I thought about using the "If" function but then I don't know the term for the data/date that has just been entered.

E.g. if the date 17/02/09 was entered how do i make the query that will got away and check if that date has been used?

 

[pbaldy]

It would look like this in SQL view:

SELECT...
FROM...
WHERE [tblSickVac].[OffStartDate]<=[Forms]![Form1]![txtToDate] And [tblSickVac].[OffEndDate]>=[Forms]![Form1]![txtFmDate]

 

[toaster045]

It would look like this in SQL view:

SELECT...
FROM...
WHERE [tblSickVac].[OffStartDate]<=[Forms]![Form1]![txtToDate] And [tblSickVac].[OffEndDate]>=[Forms]![Form1]![txtFmDate]

Thank you for your continueing help

Tbh I'm a Computing student and understand SQL is one of the various programming languages and from viewing some of my simple queries i get the very basic the idea. But any chance you could explain that expression pls in "simple" terms? I think i get the actualy idea but i'm not familar with the "sickVac" and other terms. Thanks again though. If it helps i can clear some of the data in the database and post it if it helps.

 

[pbaldy]

Those are simple table and field names in a test db I got that out of. The concept is simply "find me the records where the start date is before or equal to this end date on the form and the end date is after or equal to this start date on the form".

 

[toaster045]

Those are simple table and field names in a test db I got that out of. The concept is simply "find me the records where the start date is before or equal to this end date on the form and the end date is after or equal to this start date on the form".

ok thank you that's what i needed to get my head around.

so in my case a written form would be:

find me the records where the start date is before or equal to this end date on the form and the end date is after or equal to this start date on the form - if so return "yes this is avalible" if not "no sorry can't make this booking". - i'm starting to see this thxs.

so would the code (whether it be written in SQL form or in various boxes in the query design) be ...

SELECT...
FROM...
WHERE [tblRoomBooking].[Date of Check in ]<=[Room Booking]![Check in date]![txtToDate] And [tblRoomBooking].[Date of departure]>=[Room Booking]![Departure]![txtFmDate], "Yes it's avalible", "No not avalible"

But the fact that the dates are in short format i.e. 17/12/09 matter?

 

[pbaldy]

That query is going to find records that overlap your dates, so if it returns records you have a conflict. If it doesn't, you're okay to book. Your reference to the form controls doesn't look correct:

http://www.mvps.org/access/forms/frm0031.htm

 

[DCrake]

You may find it easier if the booking date was actually a number, in this example it is the actual day of the year not the julien number.

Add this to a module and refer to it in your query

Function DayOfYear(Anydate As Date) As Integer
    Dim DayOne  As Date
    DayOne = CDate("01/01/" & Year(Anydate))
    DayOfYear = DateDiff("d", DayOne, Format(Anydate, "Medium Date")) + 1
End Function

Query

BookingDay:DayOfYear(Format([YourDateField],"Medium Date"))

Have used Medium date to get around mm/dd conflicts

Then in your calculation use the bookingday number as the criteria

David

 

[toaster045]

You may find it easier if the booking date was actually a number, in this example it is the actual day of the year not the julien number.

That query is going to find records that overlap your dates, so if it returns records you have a conflict. If it doesn't, you're okay to book. Your reference to the form controls doesn't look correct:

http://www.mvps.org/access/forms/frm0031.htm

Thank you guys ! I really do appreciate your continuing help and how friendly and helpful people here up (not a cheesy attempts at sucking up I swear).

I'm really new to the whole codes and anything beyond A-level/GCSE access work. So I have just posted a thread in the sample databases section (waiting hopefully on approval) this database just contains a really simple - no data (apart from 2 key records) version of my main prototype.

If you have a spear 5 mins (I realize we all have busy lives) but I’d be extremely grateful if you could take a look and try and implement some of your suggestions to see if they work.

Basically - in the database (the uploaded one) I have 2 bookings for today’s date and one booking form. I'm trying (just to clarify) to make a "check availability button" which I can place on the form - but to do this I realize I will just make a macro that will run the query (hence the post in this section). I'm 17 and hopefully not too out of my depth in this matter (again whilst I prefer design views and simple tips).

Thanks Again guys.

Toby

 

[toaster045]

Thank you guys ! I really do appreciate your continuing help and how friendly and helpful people here up (not a cheesy attempts at sucking up I swear).

I'm really new to the whole codes and anything beyond A-level/GCSE access work. So I have just posted a thread in the sample databases section (waiting hopefully on approval then hopefull i can include the link here - hopefully it's the right section wasn't sure whether or not just to attach it here) this database just contains a really simple - no data (apart from 2 key records) version of my main prototype.

If you have a spear 5 mins (I realize we all have busy lives) but I’d be extremely grateful if you could take a look and try and implement some of your suggestions to see if they work.

Basically - in the database (the uploaded one) I have 2 bookings for today’s date and one booking form. I'm trying (just to clarify) to make a "check availability button" which I can place on the form - but to do this I realize I will just make a macro that will run the query (hence the post in this section). I'm 17 and hopefully not too out of my depth in this matter (again whilst I prefer design views and simple tips).

Thanks Again guys.

Toby

 

[toaster045]

OK guys I've tried the code route but it went wrong tbh it's way out of my league!

i kinda realised that there was no point in posting in the sample database - my bad

is this at all possible just using normal query designs?

 

[pbaldy]

What I posted was just a query.

 

[toaster045]

What I posted was just a query.

arr my bad.

right i tried exactly as you suggested but it didn't work and this probelm is driving me crazy

would it be possible for you to have a look at the prototype (as seen in the attachment) and see if it's still possible thxs again

 

[pbaldy]

This appears to work:

SELECT *
FROM [Room Booking]
WHERE [Room Booking].Date_of_check_in<=[Forms]![Room Booking]![Date_of_departure] AND [Room Booking].Date_of_departure>=[Forms]![Room Booking]![Date_of_check_in]

 

[gemma-the-husky]

this is what you have in one table, yes?

Booking No - Number - Long Integer
Room no - Number - Long Integer
Customer No - Number - Long Integer
Date of Check in - Date/Time - Short Date
Date of Departure - Date/Time - Short Date

then if you are trying to find a room that can accomodate a booking for

1st March to 4th March, then you need to use several queries

the way to do it is to find rooms that are in use, ignore them, and you are left with the avaialble rooms

therefore - i would find first bookings that are no good

a) rooms where bookings start before 1st March and end after 1st March,
b) rooms where bookings start before 4th March and end after 4th March

(i think this is correct, but its tricky, and needs a bit of thought - bear in mind that a room can be used again the same day of departure)

these are rooms you cant use

so use these in an unmatched query to find the others - rooms you can use

 

[toaster045]

this is what you have in one table, yes?

yep i have all them in one table

therefore - i would find first bookings that are no good

a) rooms where bookings start before 1st March and end after 1st March,
b) rooms where bookings start before 4th March and end after 4th March

yep i think thanks to PBaldy i think i've done that bit but haven't even dare add the rooms to the equation (figure of speach)

(i think this is correct, but its tricky, and needs a bit of thought

definetly agree with you there and considering this is a prototype A-Level project I might be out of my depth