05-11-06, 19:51 #1Registered User
- Join Date
- Apr 2006
Unanswered: Access Query Help - Comparing Dates
Hi there I am currently using Access to make a hotel booking system, I want to perform a query to show the current rooms and the customers details that are occupying today.
I am however having trouble with the else part of the SQL statement, how I can compare arrival date with checkout date with date() to only show customers who are stopping at the hotel at the moment?
Here is my attempt below could anyone tell me where I am going wrong
SELECT Customer.CustomerID, Customer.FirstName, Customer.Surname, Rooms.RoomNum, Rooms.RoomType, Booking.Arrival, Booking.Checkout FROM Rooms INNER JOIN (Customer INNER JOIN Booking ON Customer.CustomerID = Booking.CustomerID) ON Rooms.RoomNum = Booking.Room WHERE (DateDiff("d",Booking.Arrival,Date())>0 And DateDiff("d",Booking.Checkout,Date()) < 0);
05-12-06, 04:57 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
presumably if you interested in people staying at the hotel at present then the key factors are:-
that the customers has registered (ie booked in), not just booked a room, and that the customer has not booked out (ie left the hotel / finished their stay).
you shouldn't need to do a datediff - its irreleveant. in any event if you subtract the leave date form the satrt date you will get the number of days
it may be appropriate to use the isnull construct to detect if the leaveing date is not null. assuming of course that you only allow that field to be populated when the customer leaves. There are some db design purists who insist that null values have no validity in realtional designs, but thats another discussion.
so your tests are
there must be a valid registration (not booking)
AND the end date may be either NULL OR greater than todays date.
your problem arises if the customer gives you advance notice of leaving but hasn't left yet (some hotles allow you to book out the eveneing before - theay are still in the hotel, but the system thinks they have left. so you need to work out how you handle this case. you also ned to work out how you handle the case where soemone is leaving this day, but hasn't actually booked out. what are you looking for on the current day
hotel rooms that will be available for tonight, hotel rooms used last night, customers in the hotel.I'd rather be riding on the Tiger 800 or the Norton
05-12-06, 08:30 #3Registered User
- Join Date
- May 2006
I strongly suggest you consider using ADO and stored procedures. That way you will be doing things the same way everybody else is. Pass the stored procedure a date parameter as a date, and you will have few problems. Btw, there is a subtle bug in the current version of Access which gets stuck in a mode which doesn’t compare date values in queries. Unfortunately Microsoft has made so many enemies in the developer community that no one cares anymore whether MS knows about the problem or not. The good news is that usually by exiting and re-loading Access clears the problem up, but you might have to do a compress or de-compile, compress, re-compile to fix things. To decompile, you add the startup flag /decompile to the command line of a copy of your Access shortcut.