Reports, Subreports, and Filters

  • Thread starter Thread starter Tia
  • Start date Start date
T

Tia

I have a Report that has a subreport. I've been applying
a filter to the report. The filter I've been using is
saved as a Query. However, I've come to the conclusion
that I would have more accurate reports if I could apply
that same filter to the Subreport. Is this possible??
How would you do it???

Thanks for any information provided.
 
You may be able to use the LinkMasterFields/LinkChildFields to pass the
value down to the subreport as well.

That does not work if the filter is a range. In that case the simplest
solution is probably to set up a form where the user can enter the range,
e.g. you might type this into the Criteria of the main report's query:
Between [Forms].[MyForm].[StartDate] And [Forms].[MyForm].[EndDate]
and do the same in the subreport's query.

If the report is opened by clicking a button on a form, there is another
option for the cases where you are stuck: you can modify the SQL property of
the subreport's query before you OpenReport, e.g.:
dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = "SELECT ...
 
When I use the LinkMasterFields/LinkChildFields, it does
not group the information correctly and creates more
reports than are records in the query.

The report is opened by a form, so I'm wanting to try
your suggestion. Where do you modify the SQL property?
Can you provide an example of the actual statement so I
know what information I need to insert where? This is a
step beyond my Access knowledge so I greatly appreciate
any help that you can provide. Thanks again!
 
The Click event procedure for you command button would build the SQL
statement, and apply it like this:

Private Sub cmdPrint_Click()
Dim strSQL
strSQL = "SELECT * FROM TheTableForMySubReport WHERE ID = " & Me.ID &
";"
dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = strSQL
DoCmd.OpenReport "MyReport", acViewPreview
End Sub

If you are not sure what the SQL statement should look like, open the query
in SQL View and have a look. Be sure to use the correct delimiter around the
field type:
- for Text fields, use "
- for Date fields, use #
- for Number fields, use no delimiter.
You may be able to use BuildCriteria to help you.
 
Back
Top