Results 1 to 5 of 5
  1. #1
    kpilsbury is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Location
    St. Petersburg, FL
    Posts
    5

    Passing String Sub Query to another Query in VBA


    I am using 2 sub queries to gain current year and prior year data in my VBA code. I would like to declare the sub queries as tbl1sql and tbl2sql and then use them in a select query later.

    eg.
    SELECT *
    FROM tbl1sql cy INNER JOIN tbl2sql py
    ON cy.REC_DATE = py.RECDATE + 365

    Can this be done?

    Kevin

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    What do you mean by 'sub queries'? Are these Access query objects listed in the Navigation Pane?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kpilsbury is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Location
    St. Petersburg, FL
    Posts
    5
    Quote Originally Posted by June7 View Post
    What do you mean by 'sub queries'? Are these Access query objects listed in the Navigation Pane?
    sorry I didn't really explain it well, by sub queries I meant the following SQL strings:

    tbl1sql = " Select * from retention_historical"
    tbl2sql = "Select * From retention_prior_year"

    I would like to know if I can use them in another SQL Query for example:

    sSQL = " SELECT YEAR, PERIOD, REGION, RETENTION FROM tbl1sql t1 INNER JOIN tbl2sql t2 ON t1.PERIOD= t2.PERIOD AND t1.REGION = t2.REGION"

    The reason I want to use tbl1sql and tbl2sql instead of the actual string is because the actual SQL strings are very long. I was trying to simplify the look of the code while keeping everything in VBA.

    But if it can't be done I can always just use the queries in the Navigation Panel (which is probably the best plan) I just like everything in the same place, but that's my issue :-)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Should be possible, something I've never done. Concatenate variables. Try:

    sSQL = "SELECT YEAR, PERIOD, REGION, RETENTION FROM (" & tbl1sql & ") AS t1 INNER JOIN (" & tbl2sql & ") AS t2 ON t1.PERIOD = t2.PERIOD AND t1.REGION = t2.REGION;"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    kpilsbury is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Location
    St. Petersburg, FL
    Posts
    5
    Quote Originally Posted by June7 View Post
    Should be possible, something I've never done. Concatenate variables. Try:

    sSQL = "SELECT YEAR, PERIOD, REGION, RETENTION FROM (" & tbl1sql & ") AS t1 INNER JOIN (" & tbl2sql & ") AS t2 ON t1.PERIOD = t2.PERIOD AND t1.REGION = t2.REGION;"
    WOW! The power of 4 simple parenthesis' adding those 4 characters solved everything.

    Thanks for your help and time

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query passing to subform
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 11-20-2013, 06:46 AM
  2. passing parameters to a query
    By PatrickCairns in forum Programming
    Replies: 1
    Last Post: 12-11-2012, 08:47 PM
  3. Variable Not passing to query
    By chris.williams in forum Queries
    Replies: 2
    Last Post: 10-14-2011, 08:30 AM
  4. Passing Multi Select string to Query criteria
    By oleBucky in forum Queries
    Replies: 4
    Last Post: 05-15-2011, 02:11 PM
  5. Passing parameters to a query
    By stephenaa5 in forum Queries
    Replies: 3
    Last Post: 09-10-2010, 05:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums