a2k - returning the desired records in a subreport - need general help with my approach..

[Deano]

OK, I've been taking the detour from hell trying to sort this and after
taking a walk I think I need a rethink.

I have a report for a single employee that includes a subreport which lists
courses taken by that person.

I ask the user for Start and End dates to allow them to see only courses
that fall into that date range. I'm then trying to open the report to show
the current employee with a list of courses that fall within the date range.

I came up with some SQL that works but I can't assign it to the recordsource
of the subreport as I intended.

The only idea I have is to open the main report and filter for the employee
I want.
Then I think I need to create a stored query that filters the subreport
records and make this the subform recordsource. But I'm not sure how to
create this to include criteria for start and end dates.

What is the best approach here? Am I on the right lines here? If so any
tips about creating a suitable stored query?
Thanks for anyone's suggestions
Martin

[Allen Browne]

If you can use a form for the user to enter the limiting dates, you can
refer to the text boxes on the in the subreport's query.

For example, the Criteria Row under the CourseDate field might be:
Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

The other alternative you suggested would also work, i.e. to write the SQL
property of the QueryDef that the subreport is based on before opening the
report, e.g.:
dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = "SELECT ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Deano" <de*********@hotmail.com> wrote in message
news:PW******************@wards.force9.net...

OK, I've been taking the detour from hell trying to sort this and after
taking a walk I think I need a rethink.

I have a report for a single employee that includes a subreport which lists courses taken by that person.

I ask the user for Start and End dates to allow them to see only courses
that fall into that date range. I'm then trying to open the report to show the current employee with a list of courses that fall within the date range.
I came up with some SQL that works but I can't assign it to the recordsource of the subreport as I intended.

The only idea I have is to open the main report and filter for the employee I want.
Then I think I need to create a stored query that filters the subreport
records and make this the subform recordsource. But I'm not sure how to
create this to include criteria for start and end dates.

What is the best approach here? Am I on the right lines here? If so any
tips about creating a suitable stored query?
Thanks for anyone's suggestions
Martin

[Deano]

Allen Browne wrote:

If you can use a form for the user to enter the limiting dates, you
can refer to the text boxes on the in the subreport's query.

For example, the Criteria Row under the CourseDate field might be:
Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

The other alternative you suggested would also work, i.e. to write
the SQL property of the QueryDef that the subreport is based on
before opening the report, e.g.:
dbEngine(0)(0).QueryDefs("MySubreportQuery").SQL = "SELECT ...

Allen, you're a godsend. Thanks a million. I used the former method as i
should have done instead of coming up with increasingly complex strategies.