Passing Query Parameters in Sub-Queries

[Bob Barrows [MVP]]

coder23 wrote:

> I have a Query in Access titled "SR_Control"
>
> I know you can pass values from ASP to the Query with:
> SQL = "SR_Control '1/1/2004', '5/1/2004', 'Blue'"

'1/1/2004' ???
Shouldn't it be #'1/1/2004# ? Or are these Text parameters?

> rs1.Open SQL, cn1, etc... etc....
>
> ... but is there a way to call the Query and Pass values to it from
> an SQL string? for example: SQL2 = "SELECT * INTO test2 FROM
> (SR_Control '1/1/2004', '5/1/2004', 'Blue');" - or -
> SQL2 = "SELECT * INTO test2 FROM (" & SQL1 & ");"
>
> (the attempts above return errors.)

You need to create another saved query for this:

In Access, create a query with this sql:
SELECT * INTO test2 FROM [SR_Control]

Test it and verify that you get prompted for the parameter values.

Save it, calling it something like qCreateTableFromSR_Control. then call it
in ASP instead of SR_Control.

Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[coder23]

thanks Bob. that definitely works, but I wanted to use the SQL String because I needed to specify the name of the table.

SQL2 = "SELECT * INTO " & tableName & " FROM (SR_Control '1/1/2004', '5/1/2004', 'Blue');

is there an easy way to do this


----- Bob Barrows [MVP] wrote: ----

coder23 wrote

> I have a Query in Access titled "SR_Control

>> I know you can pass values from ASP to the Query with

> SQL = "SR_Control '1/1/2004', '5/1/2004', 'Blue'

'1/1/2004' ??
Shouldn't it be #'1/1/2004# ? Or are these Text parameters

> rs1.Open SQL, cn1, etc... etc...

>> ... but is there a way to call the Query and Pass values to it fro

> an SQL string? for example: SQL2 = "SELECT * INTO test2 FRO
> (SR_Control '1/1/2004', '5/1/2004', 'Blue');" - or
> SQL2 = "SELECT * INTO test2 FROM (" & SQL1 & ");

>> (the attempts above return errors.

You need to create another saved query for this

In Access, create a query with this sql
SELECT * INTO test2 FROM [SR_Control

Test it and verify that you get prompted for the parameter values

Save it, calling it something like qCreateTableFromSR_Control. then call i
in ASP instead of SR_Control

Bob Barrow


--
Microsoft MVP -- ASP/ASP.NE
Please reply to the newsgroup. The email account listed in my Fro
header is my spam trap, so I don't check it very often. You will get
quicker response by posting to the newsgroup