Method of Changing WHERE Clause of Complex Query

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

In Access 2000, we have a very compex sub query designed in the query
builder. This complex query merges with a 2nd simple query in a 3rd
query which becomes the recordset for a report.

We need to be able to change the Where clause of the complex query at least
3 different ways; but all of them very simple. I am trying to find a way to
do this in VB

I do not want to mess with the querydefs sql property of the complex query.
I am afraid of a bug or something else which would wreck the sql of the
query.

One possibility would be to keep the complex query without the WHERE clause.
I could then take the sql of the query, merge it with the WHERE clause,
create a new query, merge it with the 2nd query, etc. At least this would
leave the sql of the complex query intact if something went wrong.

Does anyone know of a simpler was to do this? (As I type this, I am slowly
talking myself into making a temp table holding the id's of the main table
in the complex query)

Many thanks
Mike Thomas
 
Hi Mike

You cannot alter just the WHERE clause of a query.

What we actually do for the cases you describe is to save the report unbound
(no RecordSource) and assign it in Report_Open.

Store the SQL statement up to the WHERE clause in one string, and the
remainder of the SQL statement after the WHERE clause in another:
Const strcStub = "SELECT ...
Const strcTail = "ORDER BY ...

Then stitch these together with the Where clause you need on this occasion:
strSql = strcStub & " WHERE (xxx) " & strcTail
Me.RecordSource = strSql
 
What's wrong with modifyng the QueryDef SQL?

1. Create 4 Queries. Let's call them:

Query1_Base: The "complex" Query without the WHERE Clause
Query1_Custom: any rubbish SQL will do since you will modify the SQL
Query2: your Query2
Query3: Your 3rd Query but use Query1_Custom instead of Query1 or
Query1_Base


In code you can modify the SQL String of Query1_Custom using the SQL of
Query1_Base (but Query1_Base is safe) like:

(Make sure you have DAO in the References)

Dim db as DAO.Database
Dim qdf As DAO.QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("Query1_Custom")
qdf.SQL = db.QueryDefs("Query1_Base").SQL & " WHERE ... {your criteria}"
qdf.Close

Set qdf = Nothing
Set db = Nothing

DoEvents
....

then you can open the Report that uses Query3 which will pick up the updated
Query1_Custom.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top