Not sure if this can be done... 1

[MisterMan]

I am writing an SQL query in Access to select information from one of a number of different tables. What I would like to do is have the query run and ask the user to input the month/year of the table (Which is the name of the tables stored) For example, run the query and a box pops up saying "Enter Month/Year", when it is typed it pulls the information from the appropriate table.

However, When I wrote the SQL language thusly (A small snippet)

Select InfoA, InfoB, InfoC
from [Enter Month/Year]


It didn't work because, obviously, it is looking for the table named [Enter Month/Year]. Is there anyway to make this work?

 

[PHV]

Have a look here:

http://r937.com/relational.html

If you still insists to have as many tables as month/year you may consider create a QueryDef on the fly with VBA.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MisterMan]

Not so much in the way I want to have it this way, but it's the way the people who sign my pay checks want it.

Thanks!

 

[alvechurchdata]

I don't think you'll be able to embed it all inside the SQL. The cheapest way is to use a couple of InputBoxes to ask for the Month and the Year and then build the SQL as a string:

[TT]
strMonth = InputBox("Month")
strYear = InputBox("Year")
strTable = "Table" & strYear & strMonth
strSQL = "Select * From " & strTable
Set rst = gdbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
[/TT]

You'll need to add a lot more code to check for errors and to cope with users who enter bad values for the month and the year. Personally I'd go for a form with a pair of dropdown lists and a "Go" button.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lespaul]

Not so much in the way I want to have it this way, but it's the way the people who sign my pay checks want it.

you may want to read the document PHV recommended and pass along to the people who sign your paycheck that what they are asking for isn't standard and the more data you get the harder and harder it is going to be to extract the information they need.

leslie

 

[MisterMan]

Printed the document out and mentioned it, but you know how it can be.

Thanks again!