Unanswered: Passing a table name to SQL statement as a String
I am revising an old Access 2.0 Application. I have converted it to Access 2000.
I have added code to archive old data from the active tables to archive tables. I want to be able to retrieve the data from the archive tables using existing code from a report form that gives choices on what type of data prints on the reports.
I would like to be able to use the same code and in the SQL statements where the table name is hard coded, use a variable to insert either the current table or the archive table based on a check box on the form that selects the archive data.
I created a string variable and tested for the archive data option to be checked, if it is checked I store the archive_table in the string variable. If it is not checked I store the NMR_table name in the the string variable.
When I get to the SQL statement to select the data, I tried:
sql_code = "SELECT DISTINCTROW 'stringname.NMR_Index', 'stringname.Part_Print', 'stringname.Date_In',
sql_code = sql_code & "FROM Team_Table RIGHT JOIN 'stringname' ON Team_Table.Team_Number = 'stringname.NMR_Team' "
sql_code = sql_code & "WHERE (('stringname.NMR_Number' Between [Forms]![NMR_Report_Form]![Num_Box_1] And [Forms]![NMR_Report_Form]![Num_Box_2])"
This will not work. Is there a way to pass a table name to an sql statement so I don't have to repeat all of the code for the archive reports?
You have a few syntax errors in your code:
sql_code = "SELECT DISTINCTROW 'stringname.NMR_Index'
should look something like:
sql_code = "SELECT DISTINCTROW " & stringname.NMR_Index & ...
and no comma before the FROM:
It may be best to break the statement down into basic units. Here's is a simple sample:
strSQL = "SELECT LAB_TEST_SEQUENCE "
strSQL = strSQL & "FROM BAPP_LAB_TESTS "
strSQL = strSQL & "WHERE LAB_TEST_NAME ='" & LabTestName & "'"
I suggest you recreate your select statement to look something like the above with a Select on one line, table fields follow, a From , joins and where follow. That way it would be easier to debug. The single tick mark "'" is usually reserved for use around text fields (nothing around numbers and # around date values)
Breaking your statement down into smaller units should help because I think your concept should work fine.
I changed the strTableName variable back to a table name in the SQL statements and they work great. I guess I don't know how to correctly use the variable in an SQL statement. I hope there is a way to do this, I don't want to have to repeat all of the code just for the archive data to print in the reports.