Report filter problem

[Guest]

Hello all,

I need some help with passing data to a subreport. The main report contains a
subreport that I need to pass a date variable too. From the vba code, I send
the main report a where clause and an openargs with a date. I need the
subreport to recieve this openargs date from the main report. The subreport
is linked to the main report via Master/child fields but I base the data in
the subreport on a stored query that I need to limit the records too.

Any help would be appreciated.

 

[Allen Browne]

Yes, this is an issue, since:
- There is no WhereCondition for the OpenReport.
- Attempting to set its Filter is problematic.
- You cannot use LinkMasterFields/LinkChildFields for a range.
- Parameter boxes are really annoying as the subreport is called repeatedly
where ever it is needed on the main report.

The usual solution is to use a form where the user can enter the limiting
dates. In the query that feeds the subreport, refer to the controls on the
form. Provided the form stays open in the background, the subreport will
keep reading the values from the form each time it is needed.

Typically this is just a matter of putting something like this into the
Criteria row under your date field in the subreport's query:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

To tweak this and make sure it works correctly:
1. Set the Format property of the text boxes to Short Date, so Access
understands the data type and won't accept invalid dates.

2. Declare the parameters in the query. In query design, choose Parameters
on the Query menu, and enter 2 rows in the dialog:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

3. If the field contains a time value as well as a date, change it to
greater than or equal to the start date, and less than one more than the end
date:

= [Forms].[Form1].[StartDate] And < ([Forms].[Form1].[EndDate] + 1)

4. If you want to give the user the option to leave the boxes blank, edit
the WHERE clause of the query so it looks like this:
WHERE (([Forms].[Form1].[StartDate] Is Null)
OR ([Table1].[MyDate] >= [Forms].[Form1].[StartDate]))
AND (([Forms].[Form1].[EndDate] Is Null)
OR ([Table1].[MyDate] < ([Forms].[Form1].[StartDate] + 1)))

For cases where this is still not adequate, the other option is to re-write
the query that the subreport is based on before you OpenReport, e.g.:
strSql = "SELECT ...
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql