Hi, I have had a lot of help to get this far, thanks to all concerned.

Background

 Below is some code to a VF page and an APEX controller.  The idea is somebody puts a start and an end date into a input form on a VF page and hits search. You are then shown all the booked rooms (allocated rooms) and the un-booked rooms in 2 separate lists.

Problem  - (Dates are shown in the dd/mm/yyyy format)

I need to show bookings that fall within the search criteria but might be outside of the start and end dates.  For example I have a room allocated with a start date of the 10/3/2011 and end date of 15/4/2011.

If I search to show bookings starting on the 20/3/2011 and ending on the 31/03/2011 using the code below the above example will not show. But I need it to as you can see the allocated room has some dates (i.e 20/3, 21/3, 22/3,24/3,etc...) occurring between these dates.

Possible Solution

First I think I need to list each room allocation.  Then list all the dates that occur on and between the start and end date. Then using the search start and end date list all the dates between these two dates. The cross reference both lists to see if one of the listed dates exists in both lists and return the record.  

But I have no idea if this is correct or how to take it forward.

Code

VF Page

<apex:page standardController="Room_Allocation__c"  extensions="Roommanager" tabStyle="Room_Availability_Search__tab">
    <apex:form >
    
        <apex:sectionHeader title="Room Availability Search"/>
                    
            <apex:pageBlock title="Search">
                <apex:pageBlockButtons >
                    <apex:commandButton action="{!room}" value="Search"/>
                </apex:pageBlockButtons>
                <apex:pageBlockSection columns="2">
                    <apex:inputField value="{!room.Search_Start_Date__c}"/>
                    <apex:inputField value="{!room.Search_End_Date__c}"/> 
                </apex:pageBlockSection>
            </apex:pageBlock>  
            
            <apex:pageBlock title="Booked Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!rooms}" var="room">
                        <apex:column value="{!room.Room__r.Lodge__c}"  />
                        <apex:column value="{!room.Room__r.Room_No__c}"  />
                        <apex:column value="{!room.Room__c}"  />
                        <apex:column value="{!room.Name}"  />                        
                        <apex:column value="{!room.Start_Date__c}"  />
                        <apex:column value="{!room.End_Date__c}"  />
                        <apex:column value="{!room.Nights__c}"  />
                        <apex:column value="{!room.Reservation__c}"  />   
                        <apex:column value="{!room.Reservation__r.Reservation_Contact__c }"  />                         
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 

            <apex:pageBlock title="Available Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!availablerooms}" var="avail">
                        <apex:column value="{!avail.Lodge__c}"  />
                        <apex:column value="{!avail.Room_No__c}"  />
                        <apex:column value="{!avail.Name}"  />                        
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock>             
            
            
    </apex:form>
</apex:page>

APEX Controller

Public class Roommanager {

    public PageReference room() {
        return null;
    }
    
    public List<Room_Allocation__c> listBd; 

    public Room_Allocation__c room {get;set;}
    public Roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){

            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Room__r.Room_No__c, Room__r.Lodge__c, Reservation__c, Reservation__r.Reservation_Contact__c from Room_Allocation__c r 
                where (Start_Date__c >= :room.Search_Start_Date__c 
                    and 
                    Start_Date__c <= :room.Search_End_Date__c
                    )
                    or
                    (End_Date__c >= :room.Search_Start_Date__c
                    and
                    End_Date__c <= :room.Search_End_Date__c)
                    ORDER BY Room__r.Lodge__c ASC,Room__r.Room_No__c ASC, Room__r.Lodge__c ASC];  
            
             return listBd;
 }

public List<Room__c> getAvailableRooms()
{
if(listBd == null || listBd.size() == 0)
getRooms();

List<Id> allocatedRooms = new List<Id>();

for (Room_Allocation__c roomAlloc : listBd)
allocatedRooms.add(roomAlloc.Room__c);

return ([Select Id, Name, Room_No__c, Lodge__c,(Select Id, Name, Start_Date__c, End_Date__c, Room__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms ORDER BY Lodge__c ASC, Room_No__c ASC]);

} 
}