Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable for Table Name in SQL Query (Access 2003 SP1/SP2)

    I have account data in separate tables for five year end periods (many reasons to have separate tables). Users want to compare two dates to see if one field has changed. However, they want to have flexibility in looking at dates. The tables are named tblMigDataXX/YY were XX is the month and YY the year. On a form, I ask them what they want for a beginning date and what they want for the ending date. In VBA, I use a Select Case statement to translate the dates to the proper table name. For example, if the user inputs 12/31/05 as the ending date, my variable EndingTable would have tblMigData12/05 as the value. That part of the code works.

    My SQL statement is:

    DoCmd.RunSQL "INSERT INTO tblMigClassChanges SELECT EndingTable.ASSN, EndingTable.BRANCH, EndingTable.ENT_CODE, EndingTable.CIF_NUMBER, EndingTable.LOAN_NUMBR, EndingTable.SHORT_NAME, BeginningTable.VOLUME AS PastVol, EndingTable.VOLUME AS CurrentVol, BeginningTable.CR_CLASS AS PastClass, EndingTable.CR_CLASS AS CurrClass " & _
    "FROM EndingTable INNER JOIN BeginningTable ON EndingTable.LOAN_NUMBR = BeginningTable.LOAN_NUMBR" & _
    "WHERE (BeginningTable.cr_class <> EndingTable![cr_class])" & _
    "ORDER BY EndingTable.BRANCH, EndingTable.SHORT_NAME"

    I wll be creating a report and the users want to be able to export to Excel, too, which is why I am creating a table with the results. The query is always the same; the data tables will vary.

    VBA does not recognize EndingTable and BeginningTable. Is there a way to feed SQL a table name as a variable?

    Any ideas??

    Thanks for everyone's help.

    Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable for Table Name in SQL Query (Access 2003 SP1/SP2)

    You must place EndingTable and BeginningTable outside the quotes:

    DoCmd.RunSQL "INSERT INTO tblMigClassChanges SELECT " & EndingTable & ".ASSN, " &EndingTable & ".BRANCH, " & EndingTable & ".ENT_CODE, " & EndingTable & ".CIF_NUMBER, " & EndingTable & ".LOAN_NUMBR, " & EndingTable & ".SHORT_NAME, " & BeginningTable & ".VOLUME AS PastVol, " & EndingTable & ".VOLUME AS CurrentVol, " & BeginningTable & ".CR_CLASS AS PastClass, " & EndingTable & ".CR_CLASS AS CurrClass " & _
    "FROM " & EndingTable & "INNER JOIN " & BeginningTable & " ON " & EndingTable & ".LOAN_NUMBR = " & BeginningTable & ".LOAN_NUMBR" & _
    "WHERE (" & BeginningTable & ".cr_class <> " & EndingTable& "![cr_class])" & _
    "ORDER BY " & EndingTable & ".BRANCH, " & EndingTable & ".SHORT_NAME"

    (I hope I didn't make a mistake, I didn't actually test it, but you should get the idea)

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable for Table Name in SQL Query (Access 2003 SP1/SP2)

    Hans, this is what I did:

    DoCmd.RunSQL "INSERT INTO tblMigClassChanges (ASSN, Branch, Ent_Code, CIF_Number, Loan_Number, Short_Name, PastVol, CurrentVol, PastClass, CurrClass)" & _
    "SELECT " & EndingTable & ".ASSN, " & EndingTable & ".BRANCH, " & EndingTable & ".ENT_CODE, " & EndingTable & ".CIF_NUMBER, " & EndingTable & ".LOAN_NUMBR, " & EndingTable & ".SHORT_NAME, " & BeginningTable & ".VOLUME AS PastVol, " & EndingTable & ".VOLUME AS CurrentVol, " & BeginningTable & ".CR_CLASS AS PastClass, " & EndingTable & ".CR_CLASS AS CurrClass " & _
    "FROM " & EndingTable & "INNER JOIN " & BeginningTable & " ON" & EndingTable & ".LOAN_NUMBR = " & BeginningTable & ".LOAN_NUMBR" & _
    "WHERE (" & BeginningTable & ".cr_class <> " & EndingTable & ".[cr_class])" & _
    "ORDER BY " & EndingTable & ".BRANCH, " & EndingTable & ".SHORT_NAME"

    Now, I get the error:

    Syntax Error (missing operator) in query expression 'tblMigData12/05.ASSN'

    What did I do wrong??

    Thanks!

    Nancy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable for Table Name in SQL Query (Access 2003 SP1/SP2)

    The problem is that the table name contains a non-standard character / so it must be enclosed in square brackets (for this reason, I never use non-standard characters, spaces etc. in names in Access). Try this:
    <code>
    DoCmd.RunSQL "INSERT INTO tblMigClassChanges (ASSN, Branch, Ent_Code, CIF_Number, " & _
    "Loan_Number, Short_Name, PastVol, CurrentVol, PastClass, CurrClass) " & _
    "SELECT [" & EndingTable & "].ASSN, [" & EndingTable & "].BRANCH, [" & EndingTable & _
    "].ENT_CODE, [" & EndingTable & "].CIF_NUMBER, [" & EndingTable & "].LOAN_NUMBR, [" & _
    EndingTable & "].SHORT_NAME, [" & BeginningTable & "].VOLUME AS PastVol, [" & _
    EndingTable & "].VOLUME AS CurrentVol, [" & BeginningTable & _
    "].CR_CLASS AS PastClass, [" & EndingTable & "].CR_CLASS AS CurrClass " & _
    "FROM [" & EndingTable & "] INNER JOIN [" & BeginningTable & "] ON [" & EndingTable & _
    "].LOAN_NUMBR = [" & BeginningTable & "].LOAN_NUMBR " & _
    "WHERE ([" & BeginningTable & "].cr_class <> [" & EndingTable & "].[cr_class]) " & _
    "ORDER BY [" & EndingTable & "].BRANCH, [" & EndingTable & "].SHORT_NAME"</code>

  5. #5
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable for Table Name in SQL Query (Access 2003 SP1/SP2)

    Thanks, Hans.

    I think I'll see how hard it will be to change the table names.

    I appreciate your help - you're great!

    Nancy

  6. #6
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable for Table Name in SQL Query (Access 2003 SP1/SP2)

    Hans, I changed the table names, and it works perfectly.

    Thank you again!

    Nancy

Posting Permissions

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