Solved

Parameter Query for booked rooms using MS Access

Posted on 2004-09-15
26
309 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:aris03
  • 13
  • 10
  • 3
26 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12091093
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
 

Author Comment

by:aris03
ID: 12091142
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12091430
I've created the button to check for available rooms (simple unmatched query)
the database is on its way to you
0
 

Author Comment

by:aris03
ID: 12091439
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
 

Author Comment

by:aris03
ID: 12091616
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12091658
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12091671
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 12091702
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12091704
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12091881
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12091944
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
 

Author Comment

by:aris03
ID: 12092452
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12092537
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:aris03
ID: 12094281
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12094734
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
 

Author Comment

by:aris03
ID: 12094747
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12094787
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12094803
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
 

Author Comment

by:aris03
ID: 12094820
yeah...I think..it's working now...let me just try it!!! .....thanks...
0
 

Author Comment

by:aris03
ID: 12095142
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
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 500 total points
ID: 12095797
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12096104
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
 

Author Comment

by:aris03
ID: 12099580
Sam....the parameter query is working perfectly...thanks you very much for your effort....


0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12099788
Glad I could be a help!
ThanX for the points!

Sam
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12099796
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
 

Author Comment

by:aris03
ID: 12099821
danny & sam....I really appreciate ur help....actually I can use all ur suggestions as my future references...


regards

Aris
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now