I have a table with 2 date fields startdate and enddate. A program have a start date and an end date like this 01/01/2011-25/03/2011 .

Now I want to test any date and get the program it belongs to. Say for the above, 12/01/2011 would return the above program.

I have tried 3 ways and this one seemed to have worked but I notice it is not correct always:

SELECT * FROM TBL where startdate<=#date# and enddate>=#date@
6 Years
Discussion Span
Last Post by ChrisPadgham

If this is Access, try:

Select * From TBL Where startDate <= [date] and endDate >= [date]

This would take date as a parameter and ask you for it every time its run.


Could it possibly be a problem with MSAccess interpreting your dates incorrectly? I notice you're using DD/MM/YYYY rather than MM/DD/YYYY. You might want to force the format in your query.


Yes, I think that SQL always uses mm/dd/yyyy regardless of the regional settings of the PC. Also you may like to consider that these are date/time fields so the time may be a factor if you are missing some records.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.