Table selection in query

[Rob]

Hello..

I've been searching for an answer to my query question in here before
posting my inquiry, and I've found a couple. However, I cant see any straight
answers to other people's questions so I'll be asking the same question and
hopefully, someone can help me.

I have these monthly tables in the AS/400 mainframe that contains millions
of records (thats the reason why they were setup as monthly tables). The
individual monthly tables are linked via ODBC (ie tblJan03, tblFeb03 ..
tblAug05). I want to create a single query where I can prompt the user's to
input the tablename they wish to use (ie tblFeb03 or tblJan05, etc) or even
possibly multiple months (ie tblJan05 thru tblMar05). In this query, there
will be another parameter to search for specific days or range of dates. For
example:

Select X1,X2 from tblJan05 where Inv_Date between 20050101 and 20050331

Union query will not work as the monthly table name is variable. I thought
of VB but I'm a newbie to VB and knows little on how to write codes.

Any ideas will be much appreciated..

Thanks!

The question is "why do you have multiple tables with the same structure"?
You can stick them together in a union query like:

Select "First" as TableName, Field1, Field2, Field3, Field4,...
FROM tblFirst
UNION ALL
Select "Second", Field1, Field2, Field3, Field4,...
FROM tblSecond
UNION ALL
Select "Third", Field1, Field2, Field3, Field4,...
FROM tblThird;

You can then run a query the sets a criteria by the derived TableName field.