For loop with dynamic query

[naungsai]

Dear Friends

I am trying to make dynamic query. I have a sub saying

strSQL1="Select * from qryProduct1"
strSQL2="Select * from qryProduct2"
strSQL3="Select * from qryProduct3"

for t=1 to 3

strSQL0=strSQL & t​

do something​

next t

But when I run it, it does not know the resulted strSQL0.
How do I need to concatenate to make that run as query.

Thanks in advance.

Sai KH

 

[isladogs]

You haven't explained what you are trying to do. However:

1. You need a ; at the end of each sql string

2. You haven't defined strSQL0

3. All this code will do is SELECT each item in turn but it won't do anything with them unless there is something meaningful in 'do something'

strSQL1="Select * from qryProduct1[COLOR="Red"];[/COLOR]"
strSQL2="Select * from qryProduct2[COLOR="red"];[/COLOR]"
strSQL3="Select * from qryProduct3[COLOR="red"];[/COLOR]"

for t=1 to 3
strSQL0=strSQL & t
[COLOR="red"]do something[/COLOR]
next t

Normally you would use DoCmd.RunSQL strSQL or CurrentDB.Execute strSQL
but in each case they can only DO something with an ACTION query e.g. update / append

If you are trying to JOIN these together, use a UNION query

 

[jdraw]

Always use Option Explicit in any vba procedure/subprocedure to demand explicit variable declaration.

As ridders mentioned---What exactly are you trying to do ---plain English?