Hi,
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@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.
hey,
your sql statement is the same as mine. Any other idea?
The SQL is correct, there must be something wrong with your data.
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.