Parameter Query for booked rooms using MS Access

Dear Experts.......below are the sample of my tables with the corresponding inputs and outputs....I want to use it for my parameter query...I posted same problem before and got the answers from the different experts but as I tried to use it for several times...I found out that it's incomplete, maybe because I gave them incomplete data also...Anyway..the main idea is, we are not allowed a double booking in this design and checking booked rooms using query.

TABLE BKG:                        

BKGID      DATEIN          TIMEIN              DATEOUT      TIMEOUT
1      20-Sep-04          8:00 AM        20-Sep-04      5:00PM
2      21-Sep-04          8:00 AM        25-Sep-04      5:00PM
3      16-Sep-04          8:00 AM        19-Sep-04      5:00PM
4              15-Sep-04     8:00 AM        17-Sep-04         5:00PM

TABLE ROOM:                        

ROOMID      ROOMNUMBER                  
1      1001                  
2      1002                  
3      1003                  
4      1004                  
5              1005

INPUTS                        
ENTER START DATE      15-Sep-04                  
ENTER END DATE      20-Sep-04                  
                        
DATE IN       DATE OUT      ROOM NUMBER      TIME IN      TIME OUT
                        
15-Sep-04       20-Sep-04          1002                       8:00 AM      5:00PM
15-Sep-04  17-Sep-04                    1005                      8:00 AM   5:00PM
16-Sep-04       19-Sep-04                    1004                      8:00 AM   5:00PM


INPUTS                        
ENTER START DATE      21-Sep-04                  
ENTER END DATE      25-Sep-04                  
                        
DATE IN           DATE OUT    ROOM NUMBER      TIME IN      TIME OUT
21-Sep-04           25-Sep-04     1003      8:00 AM      5:00PM
                        
INPUTS                        
ENTER START DATE      16-Sep-04                  
ENTER END DATE      19-Sep-04                  
                        
DATE IN            DATE OUT          ROOM NUMBER      TIME IN      TIME OUT
16-Sep-04            19-Sep-04               1004      8:00 AM      5:00PM
15-Sep-04            20-Sep-04               1002      8:00 AM      5:00PM
15-Sep-04       17-Sep-04                    1005                8:00 AM    5:00PM

                        
INPUTS                        
ENTER START DATE      20-Sep-04                  
ENTER END DATE      21-Sep-04                  
                        
DATE IN      DATE OUT      ROOM NUMBER      TIME IN      TIME OUT
                                                                        
*no booking for this range………..                        


INPUTS                        
ENTER START DATE      15-Sep-04                  
ENTER END DATE      17-Sep-04                  

DATE IN           DATE OUT      ROOM NUMBER      TIME IN      TIME OUT
15-Sep-04           20-Sep-04      1002                       8:00 AM      5:00PM
16-Sep-04           19-Sep-04      1004                       8:00 AM      5:00PM
15-Sep-04      17-Sep-04            1005                          8:00 AM   5:00PM

INPUTS                        
ENTER START DATE      1-Sep-04                  
ENTER END DATE      30-Sep-04                  

DATE IN             DATE OUT      ROOM NUMBER      TIME IN      TIME OUT
15-Sep-04             20-Sep-04      1002                      8:00 AM      5:00PM
21-Sep-04             25-Sep-04      1003                      8:00 AM      5:00PM
16-Sep-04             19-Sep-04      1004                      8:00 AM      5:00PM
15-Sep-04        17-Sep-04          1005                         8:00 AM    5:00PM
aris03Asked:
Who is Participating?
 
will_scarlet7Connect With a Mentor Commented:
Hi Aris,
Sorry about all the trouble. I hope this time that I have figured it out. It seems that the code in the form is setting a time as well as a date and that is what is causing the problems. Using the same query as posted above, try replacing the code in the form that sets the date used like this:

Replace all:
Me.Text20 = Me.DATEIN
with:
Me.Text20 = DateValue(Me.DATEIN)

And all:
Me.Text22 = Me.DATEOUT
with:
Me.Text22 = DateValue(Me.DATEOUT)

0
 
will_scarlet7Commented:
aris03,
Could you post a sample of your database? I don't have any ideas right now for your query, but I'd be willing to play with it if I could have a copy.

God bless!
Sam
0
 
aris03Author Commented:
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
dannywarehamCommented:
I've created the button to check for available rooms (simple unmatched query)
the database is on its way to you
0
 
aris03Author Commented:
danny..can u send it in this add: aris_palero@yahoo.com ..sorry for bothering you...the attachement was blocked by our ISP....thanks
0
 
aris03Author Commented:
Daniel….I already checked the database u made….have u tried to check the range date from 09/22/2004 to 09/25/2004? It should display the result of booked rooms from 09/20/2004 to 09/25/04………..
0
 
will_scarlet7Commented:
Danny & Aris,
Try changing the WHERE statement in the BOOKED query to:

WHERE (((BKG.DATEIN) Between [date1] And [date2])) OR (((BKG.DATEOUT) Between [date1] And [date2]));
0
 
will_scarlet7Commented:
Sorry, that should have been...
Try changing the WHERE statement of query "BOOKED1" to:

WHERE (((BKG.DATEIN) Between Forms!test!Text20-1 And Forms!test!Text22+1)) Or (((BKG.DATEOUT) Between Forms!test!Text20-1 And Forms!test!Text22+1));
0
 
dannywarehamCommented:
Sam - I fixed it already on the other post:

SELECT ROOM.ROOMNO, [BKG]![TimeOut] & " on " & [BKG]![DATEIN] AS [Booked From], [BKG]![TimeOut] & " on " & [BKG]![DATEout] AS [Booked To]
FROM ROOM INNER JOIN BKG ON ROOM.ROOMID = BKG.ROOMID
WHERE (((BKG.DATEIN)>=[Forms]![YourForm]![cboDatein] And (BKG.DATEIN)<=[Forms]![YourForm]![cboDateout])) OR (((BKG.DATEOUT)<=[Forms]![YourForm]![cboDateout] And (BKG.DATEOUT)>=[Forms]![YourForm]![cboDatein]));
0
 
will_scarlet7Commented:
Hi again,
I think there is a flaw in the logic of the check booking. As far as I can tell it is currently checking if the DATEIN and DATE out are within the entered dates on the form, however this produces incorrect results if the dates entered in the form are both in the middle of the DATEIN and DATEOUT.

Example:

DATEIN & DATEOUT 9/20/2004 and 9/24/2004
Dates entered in form: 9/22/2004 and 9/23/2004

this will not trigger the booked query because the DATEIN and DATEOUT are not within the date parameters entered in the form, even though the room is booked during that time.

My proposal:
I think to fix this you need to make a slight change and enter the Forms!test!Text20 And Forms!test!Text22 as fields in the "BOOKED1" query and set the WWHERE statement to filter on when these dates are within the DATEIN and DATE out instead ot the other way around. Like this:

SELECT BKG.BKGID, BKG.ROOMID AS BKG_roomid, BKG.DATEIN, BKG.DATEOUT, ROOM.ROOMID AS ROOM_roomid, Forms!test!Text20 AS BkStart, Forms!test!Text22 AS BkEnd
FROM ROOM INNER JOIN BKG ON ROOM.ROOMID = BKG.ROOMID
WHERE ((([Forms]![test]![Text20]) Between [BKG].[DateIn] And [BKG].[DateOut])) OR ((([Forms]![test]![Text22]) Between [BKG].[DateIn] And [BKG].[DateOut]));
0
 
will_scarlet7Commented:
Another Logic Update:

SELECT BKG.BKGID, BKG.ROOMID AS BKG_roomid, BKG.DATEIN, BKG.DATEOUT, ROOM.ROOMID AS ROOM_roomid, Forms!test!Text20 AS BkStart, Forms!test!Text22 AS BkEnd
FROM ROOM INNER JOIN BKG ON ROOM.ROOMID = BKG.ROOMID
WHERE ((([Forms]![test]![Text20]) Between [BKG].[DateIn] And [BKG].[DateOut])) OR ((([Forms]![test]![Text22]) Between [BKG].[DateIn] And [BKG].[DateOut])) OR (((BKG.DATEIN) Between [Forms]![test]![Text20]-1 And [Forms]![test]![Text22]+1)) OR (((BKG.DATEOUT) Between [Forms]![test]![Text20]-1 And [Forms]![test]![Text22]+1));


I think this should make it to include any bookings within the selected timeframe.
0
 
will_scarlet7Commented:
Explanation...
The above combines the original logic and my second proposed logic to catch both occurances in the table and in the dates entered in the form.
0
 
aris03Author Commented:
will...I checked ur SQL..it's working but there's a problem when I try to check from 09/20/2004 to 09/21/2004 ....there should be a vacant room....because the booked dates is only 09/15/2004 to 09/20/2004, 09/16/2004 to 09/20/2004 and 09/21/2004 to 09/25/2004 ..... that's the only problem with ur parameter query....
0
 
will_scarlet7Commented:
It might be the + & - from the original logic. Try this:

SELECT BKG.BKGID, BKG.ROOMID AS BKG_roomid, BKG.DATEIN, BKG.DATEOUT, ROOM.ROOMID AS ROOM_roomid, Forms!test!Text20 AS BkStart, Forms!test!Text22 AS BkEnd
FROM ROOM INNER JOIN BKG ON ROOM.ROOMID = BKG.ROOMID
WHERE ((([Forms]![test]![Text20]) Between [BKG].[DateIn] And [BKG].[DateOut])) OR ((([Forms]![test]![Text22]) Between [BKG].[DateIn] And [BKG].[DateOut])) OR (((BKG.DATEIN) Between [Forms]![test]![Text20] And [Forms]![test]![Text22])) OR (((BKG.DATEOUT) Between [Forms]![test]![Text20] And [Forms]![test]![Text22]));
0
 
aris03Author Commented:
will.........same problem with the range 09/20/04 to 09/21/04..there it shouldn't display the booked rooms because there is no booking for this range
0
 
will_scarlet7Commented:
Question then Aris,
Do you want check in date flagged as a date booked when it matches the "DATE OUT" on the form? The range 09/20/04 to 09/21/04, shows a booking because you have someone checking in on 09/21/04.
0
 
aris03Author Commented:
will....most of the hotels were charging per night not per day...so from 09/20/04 to 09/21/04 is vacant...and I think we need to put the checkin time and checkout time....checkin time is 3:00PM and checkout time is 12:00NN
0
 
will_scarlet7Commented:
OK, that can work. For some reason I thought you had said somewhere that you Checkin time was 8am and check out time was 5pm. Try this:

SELECT BKG.BKGID, BKG.ROOMID AS BKG_roomid, BKG.DATEIN, BKG.DATEOUT, ROOM.ROOMID AS ROOM_roomid, DateValue([Forms]![test]![Text20]) AS BkStart, DateValue([Forms]![test]![Text22]) AS BkEnd
FROM ROOM INNER JOIN BKG ON ROOM.ROOMID = BKG.ROOMID
WHERE (((DateValue([Forms]![test]![Text20])) Between [BKG].[DateIn] And [BKG].[DateOut]-1)) OR (((DateValue([Forms]![test]![Text22])) Between [BKG].[DateIn]+1 And [BKG].[DateOut])) OR (((BKG.DATEIN) Between [Forms]![test]![Text20] And [Forms]![test]![Text22]-1)) OR (((BKG.DATEOUT) Between [Forms]![test]![Text20]+1 And [Forms]![test]![Text22]));
0
 
will_scarlet7Commented:
Sorry, that one caused an error in the main form... Try this:

SELECT BKG.BKGID, BKG.ROOMID AS BKG_roomid, BKG.DATEIN, BKG.DATEOUT, ROOM.ROOMID AS ROOM_roomid, DateValue(Forms!test!Text20) AS BkStart, DateValue(Forms!test!Text22) AS BkEnd
FROM ROOM INNER JOIN BKG ON ROOM.ROOMID=BKG.ROOMID
WHERE (((Forms!test!Text20) Between BKG.DateIn And BKG.DateOut-1)) Or (((Forms!test!Text22) Between BKG.DateIn+1 And BKG.DateOut)) Or (((BKG.DATEIN) Between Forms!test!Text20 And Forms!test!Text22-1)) Or (((BKG.DATEOUT) Between Forms!test!Text20+1 And Forms!test!Text22));
0
 
aris03Author Commented:
yeah...I think..it's working now...let me just try it!!! .....thanks...
0
 
aris03Author Commented:
will..have u tried the ranges 09/19/2004 to 09/20,21,22/2004? also, 09/24/04 to 09/25-30/2004 - there should booked rooms in between
0
 
will_scarlet7Commented:
Hi Danny & Aris,
I think I found the source of the trouble, Both the datein & dateout controls on the form have a time as well as a date set in the "Value" property. Since the dates stored in the table did not include the time value they defaulted to 12:00 am and so the times on the form were later and would not match in the Between or <= methods. If you change the "Value" property of both controls to just "9/15/2004" then the previous code of:

Me.Text20 = Me.DATEIN
Me.Text22 = Me.DATEOUT

should work properly with the query I posted above.

God bless you both! Till tomorrow,

Sam
0
 
aris03Author Commented:
Sam....the parameter query is working perfectly...thanks you very much for your effort....


0
 
will_scarlet7Commented:
Glad I could be a help!
ThanX for the points!

Sam
0
 
dannywarehamCommented:
Morning Sam

You're right about the calendar value having a time (although it's not visible to the user).

You can select just the date value using your function - Aris, for future reference, you can also select any value from the calendar by prefixing with DAY, MONTH, YEAR etc:

DAY(me.Calendar.value) - returns the day etc...

Glad its sorted.

Danny
0
 
aris03Author Commented:
danny & sam....I really appreciate ur help....actually I can use all ur suggestions as my future references...


regards

Aris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.