1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Access Database Help

Discussion in 'HTML, Graphics & Programming' started by ArmoVanBuuren, 11 May 2006.

  1. ArmoVanBuuren

    Hitman

    Joined: 19 Mar 2005

    Posts: 569

    Currently making an access hotel booking system and am abit stuck, need to produce a report of the people checking out within 2 days

    I have used

    Code:
    DateAdd("d",2,Date()) 
    
    Which picks up check outs 2days in advance but I cant quite figure out how to do within 2days :confused:.

    Just wondering how I would do this?

    Edit.
    have tried put <= but this returns results of past bookings, how can i get it to only display checkouts from today onwards till 2 days have gone?

    Edit.

    Do I need to compare the value to the date()?

    Anyone please?
     
    Last edited: 11 May 2006
  2. ArmoVanBuuren

    Hitman

    Joined: 19 Mar 2005

    Posts: 569

    Not to worry, I solved it in the end by using this code

    Code:
    DateAdd("d",2,Date()) Or DateAdd("d",1,Date()) Or Date()
    
    Abit longer than I expected but it works fine.
     
  3. Beepcake

    Mobster

    Joined: 18 Oct 2002

    Posts: 3,882

    Location: Cheshire

    I know you've sorted it; but ff you want to re-visit it, have a look at the datediff function in the help.
     
  4. ArmoVanBuuren

    Hitman

    Joined: 19 Mar 2005

    Posts: 569

    Does Anyone how I can compare arrival date with checkout date with date() to only show customers who are stopping at the hotel at the moment?
     
  5. Beepcake

    Mobster

    Joined: 18 Oct 2002

    Posts: 3,882

    Location: Cheshire

    Well if DateDiff("d", arrivalDate, date()) was greater than or equal to 0 (or just greater than if you don't want people arriving today) then the customer would have arrived... if DateDiff("d", checkoutDate, date()) was less than or equal to 0 (or just less than if you don't want people checking out today) then the customer hasn't left yet.. combine the two with an AND and you should have people that are currently there.

    Had a few drinks, but hopefully that's right :p
     
  6. ArmoVanBuuren

    Hitman

    Joined: 19 Mar 2005

    Posts: 569

    Cheers Beepcake

    Is it something like the code below which I cant get to work

    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);
    
     
    Last edited: 11 May 2006
  7. ArmoVanBuuren

    Hitman

    Joined: 19 Mar 2005

    Posts: 569

    Solved it in the end with

    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",[Arrival],Date()))>0) AND ((DateDiff("d",[Checkout],Date()))<0));
    
    Damn Square Brackets needed for field names in where clause.
     
  8. sfx

    Hitman

    Joined: 13 Dec 2004

    Posts: 926

    You could try using:

    BETWEEN Date1 AND Date2

    Hope this helps.

    sfx