Passing Parameters in an SQL pass-through query

  • Thread starter Thread starter Angeliki
  • Start date Start date
A

Angeliki

I have written an SQL pass-through query to be run, but I
would like one of the parameters to be user defined.

For instance, in the following query:

SELECT client.name, client.surname
FROM client
WHERE MONTH(client.dob) = '12'

which is run as a pass-through query in MS Access 2000, I
would like the users to be prompted to input the month
they want.

Can you, please, tell me the correct syntax to do this.

Many thanks in advnace.
 
If you are using DAO, you can do something like:

Dim strSQL as string
strSQL = "SELECT client.name, client.surname " _
& "FROM client " _
& "WHERE Month(client.dob) = " & me.txtMonth
Currentdb.querydefs("yourQueryName").SQL = strSQL

Then execute the querydef.

If you are using ADO, I think you have to set a reference to a
querydef object, then use the .parameters property of the querydef
object to pass the parameter, but I'm not sure on that one.

Note that I did not wrap my month number in quotes, as you did, since
Month returns an integer value.

--
HTH

Dale Fye


I have written an SQL pass-through query to be run, but I
would like one of the parameters to be user defined.

For instance, in the following query:

SELECT client.name, client.surname
FROM client
WHERE MONTH(client.dob) = '12'

which is run as a pass-through query in MS Access 2000, I
would like the users to be prompted to input the month
they want.

Can you, please, tell me the correct syntax to do this.

Many thanks in advnace.
 
Dear Angeliki and Lynn:

As you have probably experienced, pass-through queries do not allow
you to insert parameters or to reference controls directly. Neither
do they allow you to use intrinsic or user-written functions. Native
Jet queries do have these features.

A good way to overcome this is to create controls on your form that
permit the entry or selection of the values the user chooses for the
query. You can then write the query to include these values. The
finished query would look just like Angeliki's sample query when
finished. The trick is that you will build this query string using
VBA coding, then submit it:

DIM sql AS String

sql = "SELECT [name], [surname] FROM client WHERE MONTH(dob) = '" & _
MyComboBox & "'"

When MyComboBox has the value 12 this will generate exactly the same
SQL code (except for newlines, which aren't so essential. You can
then apply the SQL to a RowSource, RecordSource, open a Recordset, or
use it in other ways. It can be pass-through or not.

This is possibly the most professional way to code queries. Not only
do you have control over user entries using combo boxes, which reduce
user entry errors, but the resultant code is much more "portable,"
meaning it can be made to work in a wider range of environments
(including pass-through queries). Other than learning a bit of extra
technique, it isn't particularly difficult. Finally, you have the
option to test the user entries in advance and give the user error
messages instead of just running the query and getting bad results or
undecipherable error messages.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top