Passing String Sub Query to another Query in VBA

[kpilsbury]

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

[June7]

What do you mean by 'sub queries'? Are these Access query objects listed in the Navigation Pane?

[kpilsbury]

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 :-)

[June7]

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;"

[kpilsbury]

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