Query needs additional criteria adding

[Swillsy]

Sorry think I posted this in worng section originally,

Hi All Having problems with two combo boxes on one of my forms - It works fine in the sense that I can get one to populate the options in the second one like so.

SELECT DISTINCT Rooms.RoomNo, Rooms.RoomNo
FROM Rooms
WHERE (((Rooms.Block)=[forms]![Scheduling]![Blockcombo1].[Value])

AND (((select [Rooms].[RoomNo] from [Rooms] where not exists (select * from [Rooms] where [Rooms].[RoomID] = [Schedule Details].[roomID])))<>False)); --This is not working for me

However I really want to add an 'availiability criteria' to the box so that the combo box only displays
1) Rooms in that Block
2) Available rooms in that block

I am currently querying the availability of rooms via a 'Schedule Details' table using these queries.

SELECT [Schedule Details].RoomID, [Schedule Details].StartDate, [Schedule Details].EndDate
FROM [Schedule Details]
WHERE ((([Schedule Details].StartDate)<=[Enter End Date]) AND (([Schedule Details].EndDate)>=[Enter Start Date]));

To find which rooms are booked - followed by this

SELECT [Schedule Details].RoomID
FROM Rooms LEFT JOIN BookedRooms ON Rooms.RoomID=BookedRooms.RoomID
WHERE (((BookedRooms.RoomID) Is Null));

Could I get some help adding these together As I am Currently struggling a bit

 

[georgedwilkinson]

AND (((select [Rooms].[RoomNo] from [Rooms] where not exists (select * from [Rooms] where [Rooms].[RoomID] = [Schedule Details].[roomID])))<>False)); --This is not working for me

You're right, that won't work. You're selecting data in a where clause but not doing anything with it. I would typically expect to see something like:

AND Rooms.[RoomNo] in (((select [Rooms].[RoomNo] from [Rooms] where not exists (select * from [Rooms] where [Rooms].[RoomID] = [Schedule Details].[roomID])))<>False));

I don't have enough focus this morning to address the rest of your questions. Someone else?