Room Availability SQL query seems fine but isn't!

[kajobkajob2]

hello it's kajob,
i'm new to the this forum

I'll get straight to the point,
I'm trying to make a room availability SGL query by selecting the records in my "Bookings Details" table that do not have a certain booking date.

The Details:
Booking Details; Booking ID, CheckIn, checkOut, RoomID
Rooms; RoomID

here is my code/sql stuff

SELECT Rooms.RoomID
FROM Rooms
WHERE NOT EXISTS (
SELECT Booking Details.RoomID, Booking Details.CheckIn, Booking Details.CheckOut
FROM Booking Details
WHERE ((([Enter CheckIn Date]<=[CheckOut])) AND (([Enter Checkout Date]>=[CheckIn])) AND Rooms.RoomID = Bookings.RoomID)
);


I would prefer replies to be quick, thanks for your time!

EDIT: if you've seen this thread but don't know the answer can still post DON'T KNOW so that I know your not just ignoring this page

 

[kajobkajob2]

I've checked all the brackets and spellings I've no idea what it could be

 

[bob fitz]

This is a bit of a guess but try:
SELECT Rooms.RoomID
FROM Rooms
WHERE NOT EXISTS (
SELECT Booking Details.Room ID, Booking Details.Date of stay start, Booking Details.Date of stay end
FROM Booking Details
WHERE ((([Enter Date of stay start]>=[Date of stay end])) AND (([Enter Date Of stay end]<=[Date of stay start])) AND Rooms.RoomID = Booking Details.Room ID)
);

 

[kajobkajob2]

Thanks, I'll give it a try

 

[kajobkajob2]

It still comes out with error with the "where not exists (" and everything after that.

I'm baffled as I have another acceses db with an exact replica of this query, the only thing thats different are the table and field names. here is the code for that one.

SELECT Room.RoomNumber
FROM Room
WHERE NOT EXISTS (
SELECT Bookings.RoomNumber, Bookings.Arrival, Bookings.Checkout
FROM Bookings
WHERE ((([Enter Arrival Date]<=[Checkout])) AND (([Enter Checkout Date]>=[Arrival])) AND Room.RoomNumber = Bookings.RoomNumber)
);

I've already compared the 2, finding no difference. Could some check it aswell just see if there's anything I've over looked

 

[kajobkajob2]

it's a syntax error, but I don't where I've gone wrong

 

[bob fitz]

I think field and/or table name that have spaces need to be enclosed in square brackets. For example: Booking Details.[Room ID]

 

[kajobkajob2]

I think field and/or table name that have spaces need to be enclosed in square brackets. For example: Booking Details.[Room ID]

I thought that too and I literally changed all the names into single words e.g RoomID,CheckIn,CheckOut, I also changed the SQL code accordingly

 

[kajobkajob2]

I changed the description at the top

 

[JHB]

The table name do have a space, then you have to use square brackets. For example: [Booking Details].[....]