Multi-select parameter via Query Grid

[Chutney via AccessMonster.com]

I have numerous queries that all use the same parameters. I have a form that
captures the parameters. I use individual macros for each query to call the
standard form, the specific query and export the data to an Excel spreadsheet.


The form has a multi-select listbox. I have studied as much as I can of the
exchanges on this forum about using multi-select listboxes as parameters for
queries including:
http://allenbrowne.com/ser-50.html
http://www.mvps.org/access/forms/frm0007.htm

If I understand these correctly, however, they either construct the WHERE
clause to be used in a report or build a total query, starting with SELECT.
In my case I do not use a report and I would rather not have a different
parameter form for each query or construct within the module the rather
complex queries. Therefore, I would like to find a way of passing the multi-
select parameter string to the Query Grid criteria.

There was one posting that, I think, solved this with Eval() but I have not
been able to get it to work:
http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/7851/Multi-select-Combo-Box


Is it possible to use multi-select parameters with the Query Grid and, if so,
how?

Thanks and regards

 

[Duane Hookom]

Have you considered changing the SQL property of your saved query using DAO?

Currentdb.QueryDefs("qselYourMultiselect").SQL = "SELECT... FROM... WHERE
.... IN (.....)"

 

[Chutney via AccessMonster.com]

Duane,

Thank you for your suggestion. I have never used DAO but am willing to learn.
Can I use this to change just the WHERE clause, which is what I want to do,
or do I have to create the entire SELECT statement, which is what I want to
avoid?

Regards

Have you considered changing the SQL property of your saved query using DAO?

Currentdb.QueryDefs("qselYourMultiselect").SQL = "SELECT... FROM... WHERE
... IN (.....)"

I have numerous queries that all use the same parameters. I have a form
that

[quoted text clipped - 30 lines]

Thanks and regards

 

[Duane Hookom]

You would need to update the entire SQL. The Where clause is generally not
the last part of the SQL since ORDER BY might follow.

--
Duane Hookom
MS Access MVP
--

Duane,

Thank you for your suggestion. I have never used DAO but am willing to
learn.
Can I use this to change just the WHERE clause, which is what I want to
do,
or do I have to create the entire SELECT statement, which is what I want
to
avoid?

Regards

Have you considered changing the SQL property of your saved query using
DAO?

Currentdb.QueryDefs("qselYourMultiselect").SQL = "SELECT... FROM... WHERE
... IN (.....)"

I have numerous queries that all use the same parameters. I have a form
that

[quoted text clipped - 30 lines]

Thanks and regards

 

[Van T. Dinh]

What I normally do with this is that I create 2 saved Queries. Let's called
them :

qryBase
qryCustom

Then SQL String of the qryBase simply consists of the SELECT Clause and the
FROM Clause (so basically there is no selection criteria). The SQL String
of the qryCustom can be the same but the actual SQL is not important
(provided that Access doesn't pick up any syntax errors so that you can save
the Query). The reason is that you will modify the SQL String in code.

Just before you export, you can simply construct the required SQL with code
something like:

****Untested****
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strBaseSQL As String

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qryCustom")

strBaseSQL = db.QueryDefs("qryBase").SQL
' Need to remove the semi-colon Access put in at the end of the SQL
qdf.SQL = Left(strBaseSQ, Len(strBaseSQL) - 1) & " WHERE ... "
qdf.Close


Set qdf = Nothing
Set db = Nothing
***Code ends****

(DAO Library needed)

and the qryCustom will have the required SQL that you need for your export.

Basically, you only use the name "qryCustom" but you re-construct the SQL of
this query every time you export (by code if required).

 

[Chutney via AccessMonster.com]

Van,

A very interesting approach. I was going to create a single master SELECT...
FROM as a constant attached to the form and then append a custom WHERE as
per the parameters selected via the form. This would mean a different form,
with a lot of redundancy, when I needed different base SELECT...FROMs.

Your approach, if I follow it correctly, would allow me to build multiple
unique SELECT...FROMs using the query grid and run them from one parameter
form. I presume I could have a field on the form in which the user selects
the name of the query to be used, which would allow "qryBase" to be a
variable. Or does DAO not allow a query/table name to be a variable (as with
straight MS SQL )?

Regards.

What I normally do with this is that I create 2 saved Queries. Let's called
them :

qryBase
qryCustom

Then SQL String of the qryBase simply consists of the SELECT Clause and the
FROM Clause (so basically there is no selection criteria). The SQL String
of the qryCustom can be the same but the actual SQL is not important
(provided that Access doesn't pick up any syntax errors so that you can save
the Query). The reason is that you will modify the SQL String in code.

Just before you export, you can simply construct the required SQL with code
something like:

****Untested****
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strBaseSQL As String

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qryCustom")

strBaseSQL = db.QueryDefs("qryBase").SQL
' Need to remove the semi-colon Access put in at the end of the SQL
qdf.SQL = Left(strBaseSQ, Len(strBaseSQL) - 1) & " WHERE ... "
qdf.Close

Set qdf = Nothing
Set db = Nothing
***Code ends****

(DAO Library needed)

and the qryCustom will have the required SQL that you need for your export.

Basically, you only use the name "qryCustom" but you re-construct the SQL of
this query every time you export (by code if required).

Duane,

[quoted text clipped - 7 lines]

Regards

 

[Van T. Dinh]

DAO will allow you to use a Variable for a Query name.