HELP needed with SQL - timetabling problem

I've been scratching my head for long enough, so it's time to humbly ask if someone's done it before (which is of course the case :-):

My database is a travel timetable, and has these four tables:

1. tblCity: CityID (PK), CityName

2. tblStop: StopID (PK), CityID (FK), StopName

3. tblTime: TimeID (PK), StopID(FK), RouteID (FK), arrival, departure

4. tblRoute: RouteID (PK), RouteName, startDate, endDate

I tried to keep it neat and simple. Now I need to do searches on it with two parameters: DestinationCity and TravelDate

Query should return the routes on which the Destination City is if the date falls in between those when the route is operating. I came up with this:

SELECT DISTINCT tblTime.RouteID

FROM (tblCity INNER JOIN tblStop ON tblCity.CityID = tblStop.StopCity)

INNER JOIN (tblRoute INNER JOIN tblTime on tblTime.RouteID=tblRoute.RouteID

WHERE tblRoute.startDate<TravelDate

AND tblRoute.endDate>TravelDate)

ON tblStop.StopID = tblTime.StopID

WHERE CityName='DestinationCity';

When I run this I'm getting no records, while there are some routes that operate on the given date to the chosen destination... Any clues what am I doing wrong? THANKS!

You are not really doing anything wrong travel scheduling uses ANSI SQL time interval current SQL Server does not have it but SQL Server 2005 has it. So the time part of your query is making SQL Server to return no records. I found a UDF(User defined function) that can do it. Try the link below and also check out the Time Tracker starter kit it may have something close to what you want. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||Great! So this IS a problem for me - I'm trying to do this in Access..! Any suggestions how this can be solved in Access - if at all...?|||

I have found sample code using SQL Server time interval DATEDIFF, try the links below now you will get the time and date in between. Sorry it took so long, if you have not found a solution. Hope this helps.

http://databasejournal.com/features/mssql/article.php/3076421
http://www.stanford.edu/~bsuter/sql-datecomputations.html