Selecting no records if condition is true

[Mark]

Hi - I have a numebr of rooms, which I am making available.

table: single - has single_id and single_name
rental_single has rental_id, single_id, check_in and check_out

They are linked by single_id.

I want to query so that when given two dates, it will return the
single_id that is free between two dates, which means I need to exclude
a singe_id if it has a linked entrey in the rental_single table, which
has a check_in and check_out date which iverlap with my form entries.

My query so far is:

SELECT DISTINCT single.single_id
FROM single INNER JOIN rental_single ON single.single_id =
rental_single.single_id
WHERE ((NOT ((rental_single.check_in) BETWEEN #1/1/2004# AND
#20/2/2004#))
AND
(NOT ((rental_single.check_out) BETWEEN #1/1/2004# AND #20/1/2004#)));


Trouble is, if I have an entry in the rental_single table with a check
in of #12/12/2004# and a check_out of #14/12/2004# and a checkin of
#2/1/2004# and #5/2/2004# it will still show that room linked as being
available - I want to exclude the single_id altogether, if ANY of the
entries in thje linked rental_single room fall within the two dates I am
looking for.

I'd really appreciate any pointers,

Thanks,