Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Unanswered: Query parameters

    I'm trying to use a query to find vacant rooms in an accommodation database I'm developing. I'm tearing my hair out, at least that's my excuse..:-). The following rightly puts the "Free" or "Booked" into the Test fields, but then doesn't always exclude the "booked" from the query results. I've tried various combinations and permutations without success. Can anyone put me out of my misery.....? Many thanks in advance.

    SELECT tblBooking.Arrival, tblBooking.Departure, tblRoom.Hotel, tblRoom.Room, tblRoom.Bed, IIf([Forms]![frmBook]![txtDateIn] Between [Arrival] And [Departure] Or [Forms]![frmBook]![txtDateIn]<[Arrival],"Booked","Free") AS Test, IIf([Forms]![frmBook]![txtDateOut] Between [Departure] And [Arrival] Or [Forms]![frmBook]![txtDateOut]<[Departure],"Booked","Free") AS Test2, [Forms]![frmBook]![txtDateIn] AS Arrive
    FROM tblRoom LEFT JOIN tblBooking ON tblRoom.RoomID=tblBooking.RoomID
    WHERE (((IIf(Forms!frmBook!txtDateIn Between [Arrival] And [Departure] Or Forms!frmBook!txtDateIn<[Arrival],"Booked","Free"))<>"Booked")) Or (((IIf(Forms!frmBook!txtDateOut Between [Departure] And [Arrival] Or Forms!frmBook!txtDateOut<[Departure],"Booked","Free"))<>"Booked")) Or (((tblBooking.Arrival) Is Null)) Or (((tblBooking.Departure) Is Null));

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Lots of OR conditions there, and that's where your problem lies.

    I would suggest that you start by creating a new query that simply filters out the "booked" records.

    Then re-build this query on top, using the query mentioned above instead of direct to the table.
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2005
    Posts
    6

    Thanks

    Quote Originally Posted by gvee View Post
    Lots of OR conditions there, and that's where your problem lies.

    I would suggest that you start by creating a new query that simply filters out the "booked" records.

    Then re-build this query on top, using the query mentioned above instead of direct to the table.
    Thanks for the advice, I'll certainly try that.

Posting Permissions

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