The subreport is based on a Totals query (i.e. it has a GROUP BY clause)?
If I understand you correctly, you should be able to create a query
statement that gives you the desired results. If the selection criteria need
to be applied before the aggregation is calculated, use the WHERE clause. If
it needs to be applied on the total (after aggregation), use the HAVING
clause.
If you are trying to get a count of related records in another table, you
can leave that other table out of the query and use a subqeury to get the
count. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Maybe I have not understood all you are trying to do, but if you can craft a
query that gives the desired records, you can assign it to the subreport's
query.
If you're *really* stuck, the next option is to populate a temp table with
all the records for the subreport before opening the main report, and use it
as the RecordSource.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Sheila D said:
I think that's too big a learning curve for me at the moment Allen but many
thanks anyway.
All I really want to get is the number of unique occurrences of a field
(Stand Numbers) in the report. The underlying query cannot group on this
field as has to group on Invoice ID because each invoice has several
payment
cycles. So when I count Stand Numners I actually get number of invoices
which
is not the same! I tried putting a group on Stand Number in the report
which
did give me the correct number of stands but naturally then became the
prime
sort for the report and I need it to be sorted on Company name...
I've posted several different questions to try and find a work around but
think I may be out of options now. Thanks again for suggestions
Sheila
Allen Browne said:
Hmm. The Iff() is going to get pretty complex and inefficient with 12
possible components.
The trick that we use when everything else is to awkward or slow is to
re-write the SQL property of the subreport's query before the OpenReport.
Natually this only works if the report is opened through your form's
code.
Essentially, you build up the WHERE clause of the SQL statement just like
you build up the WhereCondition string for the OpenReport of the main
report. You then assign the string to the SQL property of the query that
the
subreport is based on:
strSQL = "SELECT * FROM Table1 WHERE ...
dbEngine(0)(0).QueryDefs("YourSubreportsQueryNameHere").SQL = strSQL
HTH
Tx for reply, this works fine as long as the user makes a choice in
each
field. For the main report after selections are made I have a macro
which
opens the report and applies 1 of 12 filters based on queries which
get
there values from the form. The queries disregard fields where noo
selection
is made. (This may not be very elegant but was the best I could come up
with
to achieve my objective.
So is there a way in the sub report underlying query to ignore the
empty
fields (I think I may have answered my own question there - maybe I can
use
an IIF statement?) Or is there an easier way?
Thanks for your help
Sheila
:
The simplest way to do this is to have the subreport's query read the
value
from the form.
Open the query that the subreport is based on, in design view.
In the Criteria row under the field that needs to be filtered, enter
something like this:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
I have a report which displays records accoring to various selections
made
by
user from front end form. I would like to incorporate a sub report
into
the
main form as there is 1 piece of data I cannot get from the report
as
it
is.
I need this to apply the same filter. Can this be done - I'm not a
VB
person
so something simple if possible would be appreciated.
