Parameter (Criteria) in Text box in Report Header

[KevinT]

Hi,

I've read a few of the messages about this but they don't seem to answer my
question.

To explain,
I wish to have a list created in a Report Header that shows all the Depots
the report relates to. There may be 100 or more records for each Depot so I
want to only show unique values, or a particular Depot once. I don't want 100
or more entries in this list.
I also use the "& *" to save people typing the whole name, which can be
quite long.

For example,
Burwood Depot may show in the detail 50 times. I only want 1 entry for
Burwood Depot in my list.
Can it be done?

TIA,

Kevin

 

[Duane Hookom]

You can either change your report's recordsource to a group by query or you
can create a grouping level on Depot and then hide the detail section.

 

[John Spencer]

It sounds as if you need a sub-report in the report header (or in a group
header) to list all the depots that are in the main report. You should be
able to design a report that uses the same criteria as your main report
(with details).

I would use an unbound form to set the criteria for the report.

The source for the subform would be a distinct query that returned just the
Depots field.
SELECT DISTINCT Depot FROM YourTable WHERE SomeField =
Forms![YourCriteriaForm]![CriteriaTextBox]

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KevinT]

Thanks guys,

I'll try it out and let you know how I get on.

Kevin

It sounds as if you need a sub-report in the report header (or in a group
header) to list all the depots that are in the main report. You should be
able to design a report that uses the same criteria as your main report
(with details).

I would use an unbound form to set the criteria for the report.

The source for the subform would be a distinct query that returned just the
Depots field.
SELECT DISTINCT Depot FROM YourTable WHERE SomeField =
Forms![YourCriteriaForm]![CriteriaTextBox]

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..