vba - Variable Date Ranged Data Sets with SQL Statement? Used with MS Access Tables -

i have large series of datasets. why when imported, automatically divided tables year.

ex: data 2014, goes table "2014" if data 2013, goes table "2013", on own. if table not exist, creates table, , inserts "2014" in tblyears keep track of tables have been created.

my issue: i'm trying allow user able look-up date ranges of data. maybe wants see data spanning across february 2013 march 2015.

so i'm sure can find sql query clause look-up data within date range user gives, issue arises if date range spans across multiple years. how tell sql statement include tables? let's has date range of 2013, 2014, , 2015. how make sure (variably speaking) can include tables in from?

you create query of relevant years every time user opens db. way if years added there. , since dropped/recreated every time open file date.

something like

dim db dao.database dim qnewunion dao.querydef dim rsyears dao.recordset dim sqlunion string, unionallstring string, unionqueryname string  unionqueryname = "allresultyears" unionallstring = " union "  set db = currentdb set rsyears = db.openrecordset("your years table")  'make union sql using relevant years while not rsyears.eof     sqlunion = sqlunion & vbnewline & " select * results_" & rsyears("year") & unionallstring     rsyears.movenext loop rsyears.close  'lop off trailing union sqlunion = left(sqlunion, len(sqlunion) - len(unionallstring))  set qnewunion = new querydef qnewunion.sql = sqlunion qnewunion.name = unionqueryname  'delete query if exists on error resume next db.querydefs.delete unionqueryname on error goto 0  'add new query db.querydefs.append qnewunion 

then use allresultyears query basis search.

of course more expensive putting data in 1 table. that's optimal solution.