Can you just confirm what you mean by a 'Tour'? - eg: you offer a 3-day holiday in Florida and that is one record, you offer a 3-day holiday in New York and that is a second record, and you offer a 2-week holiday in Hawaii and that is a third record. During that 2-week holiday in Hawaii you offer an excursion to the beach on the 2nd day and an excursion to the mountains on the 4th day, but those dates and excursions are recorded on the 'Hawaii Holiday' record.
That is distinct from you having one Table with the Florida, NY, and Hawaii holidays as one record each, and a related table of Excursions, each Holiday Record having many Excursions linked to it.
If I assume that your 'Tour' is my 'Holiday' and all you want is to find 'Holidays' that will be in progress on a certain date, then a manual 'Find' would be to search for all TourStartDates <= [your chosen date] and on the same find request TourEndDates >= [your chosen date].
If that provides you with the list you want then it can easily be scripted so you just ask the user to select a date from the pop-down calendar of a gDateToLookFor field, set a script trigger for that field being changed, and make it go into 'Find' mode, set the two date fields with the date they entered, etc.
Thanks a lot for the reply, certainly put things into perspective. You are correct as i would just like to view which 'holiday' is in progress on a particular date.
Unfortunately, I have very limited knowledge with the scripting part. I can only get as far as creating "pop-down calendar of a gDateToLookFor field" :)
Can you please help me "set a script trigger for that field being changed, and make it go into 'Find' mode,"
I'm hopeless trying to figure out the script for that manual find.
In your Tours table you have created a field called gDateToCheck of type 'Date', and storage options 'Global'.
A simple script would be (sorry, you'll have to translate my typed steps into the equivalent Script Steps):
## Capture the gDateToCheck field (This is just a comment to help you follow your own script later)
Set Variable ($DateToCheck = gDateToCheck)
Enter Find Mode
## Set the selected date into the TourStart and TourEnd date fields
Insert Text (TourDateStart ; <= ) -------- leave ticked the 'Select Entire Contents' option
Insert Calculated Result (TourDateStart ; $DateToCheck ) -------- UNTICK the 'Select Entire Contents' option
Insert Text (TourDateEnd ; >= ) -------- leave ticked the 'Select Entire Contents' option
Insert Calculated Result (TourDateEnd ; $DateToCheck ) -------- UNTICK the 'Select Entire Contents' option
Perform Find 
## Go to the list layout to show the results
Go To Layout (TourList)
Does that give you enough info? You can build on it by for example checking if no tours are running on that date and giving a dialogue box feedback to the user, or just take them directly to the Tour Form view if there is only one tour, anyway.
You can set a script trigger on the gDateToCheck field that runs that 'find' script when the date is changed.