One selection clause for many queries

[dougie]

I have several queries (crosstabs) which are eventually combined together to form the data for a single report. If I want to date select for this report I need to add a WHERE clause to every query via VBA. This isn't difficult but it set me wondering if there was any way to have one dummy query containing the necessary WHERE statement, and this somehow gets appended to all the other queries, i.e. to filter on date I only need a change in one place. Any ideas?

[orange]

See Martin Green's tutorials where he introduces and leads you through some interesting Sql and vba examples. There are a number of them, but they start here
http://www.fontstuff.com/access/acctut14.htm

[dougie]

Thanks, a good resource, but a quick read through didn't find what I want. I need something like:

SELECT * FROM <tablename> WHERE (SELECT Criterion condition from somewhere else)

The subquery is appended to every query but it's actual value is stored and controlled in one place, e'g' a field in a table. For example, I create a table called refTable containing one text field called 'condition' and which has (say) this value "month(recorddate)=6" and then write:

SELECT * FROM <tablename> WHERE (SELECT [condition] from refTable)

...but sadly this dosn't work.

[orange]

This is along the line of the use of vba to create the sql you want to use.
I have a table HIRES with fields
name text
hiredate date/time

My RefTable has fields
id autonumber
RefClause text

Sub reftest()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sql As String 'Base sql statement
sql = "SELECT * FROM HIRES WHERE "
Set db = CurrentDb
Set rs = db.OpenRecordset("select refclause from refTable where id = 1")
rs.MoveFirst 'only get 1 record back
sql = sql & rs!refclause 'append the where condition to the base sql
Debug.Print sql
End Sub

[dougie]

Thanks - excellent.

[dougie]

No - of course it's easy to write VBA and complete the SQL string - sorry if I didn't make myself clear. What I want is the SQL view of the query to show 'blah blah blah WHERE ' & <condition pulled from somewhere>. This query never gets edited, only the 'somewhere' it pulls its WHERE clause from.

[orange]

This query never gets edited, only the 'somewhere' it pulls its WHERE clause from.

How does one pull in the where clause and put it together with the base sql and NOT EDIT the query? Perhaps you should tell us your definition of edit.

In the example I posted to show a concept, you could have a number of "where clauses", but, somehow, you have to know which one to apply whenever you want to use it in the query.

Going back to your statement that I quoted above, you would need to know which where clause to apply; get it from somewhere and apply it somehow - in my view.

I'd like to see a working example of what you are conceiving in the"one selection clause many queries".

[dougie]

>Perhaps you should tell us your definition of edit

You'll get a definition of 'edit' here - my apologies if English isn't your first language.

Look at my second post, which uses a subquery to supply a 'WHERE' clause. It *almost* works, and I've used db languages (e.g. Clipper) where such a thing would work (blocks of code may be held in variables and compiled on the fly). To be a bit more explicit, I want to write this:

SELECT * FROM <tablename1> WHERE (SELECT [fieldname] from <tablename2>)

[fieldname] in tablename2 contains some text such as '[area]="north"'

Tantalisingly, a statement like:

SELECT * FROM <tablename1> WHERE [area]=(SELECT [fieldname] from <tablename2>)

...and fieldname contains "north", does work.