Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    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?

    Thanks,

    SBR

  2. #2
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    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:

    stringname.Date_In & " "

    sql_code = sql_code & "FROM ...

    ...rtk

  3. #3
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Thanks for the suggestions. I have tried using the &'s before and it looked like this:

    "SELECT DISTINCTROW ' & strTableName.NMR_Index & ', ' & strTableName.Part_Print & ', ' & strTableName.Date_In & ', ' & strTableName.Date_Due & ', ' & strTableName.Date_Complete & ', ' & strTableName.Defect_Code & ', Team_Table.Team_Name, ' & strTableName.Days_Past_Print & ', ' & strTableName.CA_Assignee & ' "
    sql_code = sql_code & "FROM Team_Table RIGHT JOIN ' & strTableName & ' ON Team_Table.Team_Number = ' & strTableName.NMR_Team & ' "
    sql_code = sql_code & "WHERE ((' & strTableName.NMR_Number & ' Between [Forms]![NMR_Report_Form]![Num_Box_1] And [Forms]![NMR_Report_Form]![Num_Box_2])"

    When I execute this code, I get an error message "Joint Expression not supported".

    Do you see anything wrong with this code?

    Thanks,

    SBR

  4. #4
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    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.
    ...rtk

  5. #5
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    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.

    Thanks for your suggestions.

    SBR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •