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
