I have a table that includes a column for date, bay/dept, and estimated cost.  I created a query that prompts me for the year then month as it opens - it then gathers entries for that month, groups and counts the bay/dept incidents and sums up the estimated cost for those incidents:

SELECT DISTINCTROW qryBayPromptMonthYear.[BAY/DEPT], qryBayPromptMonthYear.Expr1, qryBayPromptMonthYear.Expr2, Sum(qryBayPromptMonthYear.[CountOfBAY/DEPT]) AS [Sum Of CountOfBAY/DEPT], Sum(qryBayPromptMonthYear.[ESTIMATED COST]) AS [Sum Of ESTIMATED COST]
FROM qryBayPromptMonthYear
GROUP BY qryBayPromptMonthYear.[BAY/DEPT], qryBayPromptMonthYear.Expr1, qryBayPromptMonthYear.Expr2;

The report I created from this query automatically prompts me for the year and month upon opening and lists bay/dept, no. of incidents, and estimated cost that month for each bay/dept.  All of this works great.

The issue is when I try to place a pie chart on the report using this same query.  I can put the chart in and make it work, but when I open the report with the chart the report prompts me for the year and month MULTIPLIED by the number of bay/dept's that had incidents for that month (example:  if 3 depts (out of 20) had problems that month - the report will prompt me for the year and month 3 times before it opens).  Without the chart it works fine.  

Do you know a fix for this issue?  

Thank You,
Holly

---

Answer from Richard Rost:

Here's what I would do:

Make an unbound form that has your criteria on it as unbound fields. So if you have two values (myYear, myMonth) then just make two text boxes.

Now, in your query, use that form for the values:

=Forms!MyFormName!myYear

Now, when your queries and reports run, they'll just get the values from that form and you only have to type them in ONCE.

If you want to refer to a value on a Form, you can use the notation:

Forms!FormName!FieldName

You can use this notation inside of queries, other forms, or even reports. I have a free tutorial that explains how to use this:

http://599cd.com/tips/access/forms-formname-field-notation?key=BlFAlEx

Note that if you're refering to a value on a SUBFORM, the correct notation is:

Forms!FormName!SubForm.Form!FieldName

Crazy, I know. Enjoy! Hope this helps.