Here's an example of building the WHERE clause in code:
http://allenbrowne.com/ser-62.html
The example applies the clause as a Filter for a form (or a WhereCondition
for OpenReport), but for case (c) you would be patching the WHERE clause
into a string and assigning to the SQL property of the QueryDef. Not too
difficult. Very flexible.
Option (b) is perhaps not obvious, but say your subreport currently has
properties like this:
Master Link Fields: ClientID
Child Linke Fields: ClientID
You add the name of 2 more text boxes:
Master Link Fields: ClientID; txtMain
Child Linke Fields: ClientID; txtSub
where txtMain is the text box on the main report that has a control source
of = -1, and txtSub is the expression in the subreport that is effectively
your filter string. For the cases where txtSub evalutes to True, it matches
the True (-1) in txtMain on the main report and so the records are shown.
For completeness, we probably should also mention option (d): write the
subreport's records to a temporary table before opening the report, and use
it as the source.
I've used all 4, Stephen: basically in that orders, i.e. (a) where possible,
then (b), then ...
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Stephen @ ZennHAUS said:
Hi Allen
Tried option A and it works well for a simple query that has either all or
none of the criteria specified. To add up to 11 criteria randomly
depending
on the needs of the user at the time has proven way too complex and time
consuming especially when I have 22 reports to do this for each with
equally
complex but different queries which would require fresh code for each one.
Option C looks like a good option if it weren't for the massively complex
(a
sample is below), recreating it for each report from code with variances
depending on the criteria selected by the user would be ... OMG.
Option B looks even better, but I am not quite sure of what you are
suggesting. My Parent report is unbound, so at this stage without doing
something tricky, I cannot link them.
a) Open a form that provides the parameters for the subreport's source
query.
The criteria in the query will look like this:
[Forms].[Form1].[Text0]
By filling in the text boxes on the form before opening the report, the
subreport than filters as you desire.
b) Use MasterLinkField/ChildLinkFields creatively.
For example, place a text box on the main report with Control Source of:
= -1
Then in the subreport, add a text box that works as your filter condition
(evaluating as True or False (or null)), e.g.:
=([SomeField] Between #1/1/2008# And #1/1/2009#)
Then add these text box names to the MasterLinkField and ChildLinkFields
properties of the subreport control.
c) Change the SQL property of the subreport's source query.
Before you open the report, do this kind of thing:
strSql = "SELECT * FROM Table1 WHERE " & _
"([SomeField] Between #1/1/2008# And #1/1/2009#);"
CurrentDb.QueryDefs("MySubreportQuery").SQL = strSql
DoCmd.OpenReport ...
