Prompting for the same parameter three times when opening a report.

  • Thread starter Lee B via AccessMonster.com
  • Start date
L

Lee B via AccessMonster.com

I have a subreport that is based on a summary query that is based on the same
query as the main report. It is my date parameter "Between [Enter start date:
yyyy/mm/dd] And [Enter end date:yyyy/mm/dd]" that pops up the three times.
If I run the subquery or the main query or the subreport separately, I don't
have this problem; it's only when I run the report with the subreport as part
of it that it doesn't work. Quite frustrating. I've tried putting the date
parameter in the subquery instead of the main query and that doesn't work
either. Here are the names of the reports and queries:
"rpt - InvoiceCGross" based on "qry - selectcustodialdatagross"
"qry - custodialsummarygross" based on "qry - selectcustodialdatagross"
"srpt - custodialsummarygross" based on "qry - custodialsummarygross"
Any help would be greatly appreicated.
Thanks!
Lee
 
A

Allen Browne

Lee, a simple workaround is to get your queries to read the value from a
form.

If you create a form named (say) Form1, with 2 text boxes named StartDate
and EndDate, you can put this in the Criteria row of your query under the
relevant fields to get it to read the values from the open form:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

BTW, if these criteria are being applied to Date/Time fields, and you are
trying to ensure Access does not misunderstand the date formats, you can
prevent that by:
1. Declaring the parameters:
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

2. Formatting the unbound text boxes.
Set the Format property of the 2 text boxes on the form to:
Short Date
so Access recognises the data type, and won't accept invalid dates.
 
Top