Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    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
    Code:
    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);

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  3. #3
    Join Date
    May 2006
    Posts
    6

    Stored Procedures

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •