Can I Pass Query Parameters Through SQL Statements?

[Van T. Dinh]

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)

 

[Ken Snell]

One way to do it (assumes that CompanyID is a numeric format and not text
format):

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & InputBox("Which company number?") & ";"

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

Let me put this in concrete terms. Suppose I have a parameterized query
that looks like this:

SELECT MAX(RecordID) FROM MyTable WHERE CompanyID = [Which company number?];

How would I structure an SQL query to call this query passing the CompanyID
in the SQL string? For what it's worth, the query above will be used as a
subquery nested inside another query.

Thanks!
John

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)

 

[Ken Snell]

From where does the query get the parameter's value if not from the user?
The parameter in your query appears to be one that requests the info from
the user. Will the value come from a variable in the code? If yes:

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & NameOfTheVariable & ";"

--

Ken Snell
<MS ACCESS MVP>

Well, the idea is to bypass input from the user...no prompts whatsoever. I
have a parameterized query, and I need to be able to pass the parameter in
the SQL string itself. I'm assuming this just can't be done.

Thanks for your reply!

John

One way to do it (assumes that CompanyID is a numeric format and not text
format):

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & InputBox("Which company number?") & ";"

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

Let me put this in concrete terms. Suppose I have a parameterized query
that looks like this:

SELECT MAX(RecordID) FROM MyTable WHERE CompanyID = [Which company number?];

How would I structure an SQL query to call this query passing the CompanyID
in the SQL string? For what it's worth, the query above will be used as a
subquery nested inside another query.

Thanks!
John


:

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Hello,

I have a parameter query, and really need to be able to
pass the parameters
through an SQL statement rather than an ADO property, or
anything similar to
that. I'm guessing that since my search thus far hasn't
returned any results
indicating that this can be done, that it must not be
possible.

Does anyone know if this is possible?

Thanks MUCH!

John
.

 

[Tim Ferguson]

Well, the idea is to bypass input from the user...no prompts
whatsoever. I have a parameterized query, and I need to be able to
pass the parameter in the SQL string itself. I'm assuming this just
can't be done.

It's not very hard:-

strWhichCompanyNumber = GetMagicalStringFromSomewhere()

strSQL = "SELECT MAX(RecordID) " & vbNewLine & _
"FROM MyTable " & vbNewLine & _
"WHERE CompanyID = """ & strWhichCompanyNumber & """"


Set rs = db.OpenRecordset(strSQL, etc, etc)


Hope it helps


Tim F

 

[Ken Snell]

I've reread the thread and am not understanding why what Tim and I have
posted won't work for you. I didn't see any comment about subquery in the
thread... perhaps you're referring to another thread?

I wouldn't say that what you want to do cannot be done, but... good luck.

--

Ken Snell
<MS ACCESS MVP>

No, it does not come from the user. If you read my reply (way below) to Van
T. Dinh, this query is to be used as a subquery. The subquery is part of a
join operation. It NEEDS to work exactly as I said it does (by passing a
parameter embedded in an SQL string), but there just must be no way to do
that, so I'll go back to the drawing board.

I appreciate your willingness to help though.

From where does the query get the parameter's value if not from the user?
The parameter in your query appears to be one that requests the info from
the user. Will the value come from a variable in the code? If yes:

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & NameOfTheVariable & ";"

--

Ken Snell
<MS ACCESS MVP>

Well, the idea is to bypass input from the user...no prompts

whatsoever.
I

have a parameterized query, and I need to be able to pass the parameter in
the SQL string itself. I'm assuming this just can't be done.

Thanks for your reply!

John

:

One way to do it (assumes that CompanyID is a numeric format and not text
format):

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & InputBox("Which company number?") & ";"

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

Let me put this in concrete terms. Suppose I have a parameterized query
that looks like this:

SELECT MAX(RecordID) FROM MyTable WHERE CompanyID = [Which company
number?];

How would I structure an SQL query to call this query passing the
CompanyID
in the SQL string? For what it's worth, the query above will be

used
as a

subquery nested inside another query.

Thanks!
John


:

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Hello,

I have a parameter query, and really need to be able to
pass the parameters
through an SQL statement rather than an ADO property, or
anything similar to
that. I'm guessing that since my search thus far hasn't
returned any results
indicating that this can be done, that it must not be
possible.

Does anyone know if this is possible?

Thanks MUCH!

John
.

 

[Guest]

Hehehe...you didn't READ it, Ken. It's in this very message. Here's a copy
and paste from below:

Thanks anyway.

I've reread the thread and am not understanding why what Tim and I have
posted won't work for you. I didn't see any comment about subquery in the
thread... perhaps you're referring to another thread?

I wouldn't say that what you want to do cannot be done, but... good luck.

--

Ken Snell
<MS ACCESS MVP>

No, it does not come from the user. If you read my reply (way below) to Van
T. Dinh, this query is to be used as a subquery. The subquery is part of a
join operation. It NEEDS to work exactly as I said it does (by passing a
parameter embedded in an SQL string), but there just must be no way to do
that, so I'll go back to the drawing board.

I appreciate your willingness to help though.

From where does the query get the parameter's value if not from the user?
The parameter in your query appears to be one that requests the info from
the user. Will the value come from a variable in the code? If yes:

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & NameOfTheVariable & ";"

--

Ken Snell
<MS ACCESS MVP>

Well, the idea is to bypass input from the user...no prompts whatsoever.
I
have a parameterized query, and I need to be able to pass the parameter in
the SQL string itself. I'm assuming this just can't be done.

Thanks for your reply!

John

:

One way to do it (assumes that CompanyID is a numeric format and not
text
format):

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & InputBox("Which company number?") & ";"

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

Let me put this in concrete terms. Suppose I have a parameterized
query
that looks like this:

SELECT MAX(RecordID) FROM MyTable WHERE CompanyID = [Which company
number?];

How would I structure an SQL query to call this query passing the
CompanyID
in the SQL string? For what it's worth, the query above will be used
as a
subquery nested inside another query.

Thanks!
John


:

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Hello,

I have a parameter query, and really need to be able to
pass the parameters
through an SQL statement rather than an ADO property, or
anything similar to
that. I'm guessing that since my search thus far hasn't
returned any results
indicating that this can be done, that it must not be
possible.

Does anyone know if this is possible?

Thanks MUCH!

John
.

 

[Ted Allen]

Hi John,

I've read the threads and I think what may be confusing
myself and the others is why you would have a parameter
if you do not want the input to come from the user.
Usually a parameter is used to prompt the user.

Tim and Ken have both mentioned ways of constructing the
string based on either user input or a variable value.

It seems like what you are trying to do is probably
simple, but I think it is hard for anyone to give you an
exact answer because it isn't clear where you will be
drawing the value that you are referring to as a
parameter (would this come from a form control value, a
calculated value, etc?). Or, backing up even further, it
may help to just explain the purpose of your subquery
within the query. It could be that what you want to do
can be done without a parameter.

HTH, Ted Allen

-----Original Message-----
No, it does not come from the user. If you read my reply (way below) to Van
T. Dinh, this query is to be used as a subquery. The subquery is part of a
join operation. It NEEDS to work exactly as I said it does (by passing a
parameter embedded in an SQL string), but there just must be no way to do
that, so I'll go back to the drawing board.

I appreciate your willingness to help though.

From where does the query get the parameter's value if not from the user?
The parameter in your query appears to be one that requests the info from
the user. Will the value come from a variable in the code? If yes:

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & NameOfTheVariable & ";"

--

Ken Snell
<MS ACCESS MVP>

Well, the idea is to bypass input from the user...no

prompts whatsoever.
I

have a parameterized query, and I need to be able to pass the parameter in
the SQL string itself. I'm assuming this just can't be done.

Thanks for your reply!

John

:

One way to do it (assumes that CompanyID is a

numeric format and not
text

format):

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & InputBox("Which company number?") & ";"

--

Ken Snell
<MS ACCESS MVP>

news:80C4FD2E-146B-4D9C-BAED- (e-mail address removed)...
Thanks for your reply!

Let me put this in concrete terms. Suppose I

have a parameterized
query

that looks like this:

SELECT MAX(RecordID) FROM MyTable WHERE CompanyID = [Which company
number?];

How would I structure an SQL query to call this query passing the
CompanyID
in the SQL string? For what it's worth, the

query above will be used
as a

subquery nested inside another query.

Thanks!
John


:

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Hello,

I have a parameter query, and really need to be able to
pass the parameters
through an SQL statement rather than an ADO property, or
anything similar to
that. I'm guessing that since my search thus far hasn't
returned any results
indicating that this can be done, that it must not be
possible.

Does anyone know if this is possible?

Thanks MUCH!

John
.

.

 

[Ken Snell]

You're right... I did miss that sentence. My apology.

However, can you explain what the source of the parameter value is? If it's
not coming from the user, then where is it coming from?

--

Ken Snell
<MS ACCESS MVP>

Hehehe...you didn't READ it, Ken. It's in this very message. Here's a copy
and paste from below:

be
used

Thanks anyway.

I've reread the thread and am not understanding why what Tim and I have
posted won't work for you. I didn't see any comment about subquery in the
thread... perhaps you're referring to another thread?

I wouldn't say that what you want to do cannot be done, but... good luck.

--

Ken Snell
<MS ACCESS MVP>

No, it does not come from the user. If you read my reply (way below)

to
Van

T. Dinh, this query is to be used as a subquery. The subquery is part

of
a

join operation. It NEEDS to work exactly as I said it does (by passing a
parameter embedded in an SQL string), but there just must be no way to do
that, so I'll go back to the drawing board.

I appreciate your willingness to help though.

:

From where does the query get the parameter's value if not from the user?
The parameter in your query appears to be one that requests the info from
the user. Will the value come from a variable in the code? If yes:

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & NameOfTheVariable & ";"

--

Ken Snell
<MS ACCESS MVP>

Well, the idea is to bypass input from the user...no prompts whatsoever.
I
have a parameterized query, and I need to be able to pass the parameter in
the SQL string itself. I'm assuming this just can't be done.

Thanks for your reply!

John

:

One way to do it (assumes that CompanyID is a numeric format and not
text
format):

Dim strSQL As String
strSQL = "SELECT MAX(RecordID) FROM MyTable " & _
"WHERE CompanyID = " & InputBox("Which company number?") & ";"

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

Let me put this in concrete terms. Suppose I have a parameterized
query
that looks like this:

SELECT MAX(RecordID) FROM MyTable WHERE CompanyID = [Which company
number?];

How would I structure an SQL query to call this query passing the
CompanyID
in the SQL string? For what it's worth, the query above will

be
used

as a
subquery nested inside another query.

Thanks!
John


:

You can construct the SQL String (with parameters replaced
by explicit values) in code and then use this String as
the Source (for Recordset.Open ?)

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Hello,

I have a parameter query, and really need to be able to
pass the parameters
through an SQL statement rather than an ADO property, or
anything similar to
that. I'm guessing that since my search thus far hasn't
returned any results
indicating that this can be done, that it must not be
possible.

Does anyone know if this is possible?

Thanks MUCH!

John
.

 

[Van T. Dinh]

From what I read, you seem to want to get the "Parameter"
from the main Query??? It may not be officially a
Parametrised Query in this case (see Ken's & others'
posts).

It is possible to refer to Field (value) from the main
Query in the SubQuery. This is called the *correlated*
SubQuery.

I would suggest you post the relevant Table details, what
you want to get out of the Query and the SQL string of
your attempts to create the *main* query (which should
include the SubQuery SQL)

HTH
Van T. Dinh
MVP (Access)