I'm using Access 2003 - ADP against SQL Server 2000 using ADO
I have the following problem I'm trying to solve:
I'm trying to derive values from a single table that match month and year
start_month = 10
start_year = 2005
end_month = 5
end_year = 2006
I want to select all records that are dated between start_month/start_year (e.g. 10/2005) and end_month/end_year (e.g. 5/2006)
The only way I can figure out how to do this is to do a true intersect
Select the_id from table1 WHERE ss_month >= 5 AND ss_year >= 2005
Select the_id from table1 WHERE ss_month <= 6 AND ss_year <= 2006
This, however, does not work in Access. The individual queries work fine, and they work fine when using UNION (which gives erroneous data), but the query does not work with INTERSECT.
Is there a query workaround for this?