Charts used as Report with Date Parameter 1

[summer01]

I am wondering if it is possible to have 2 charts made from the same query on one Report page (no record source for the blank report where sub report charts added) where a date parameter can be entered once and cover both charts. I tried a few basic things, including attaching the main report to the query for the date alone and then adding the charts, but nothing worked. Below is the SQL used to make both charts.

SELECT tblPhoneCalls.[Supp ID], tblPhoneCalls.[Type Of Call], tblPhoneCalls.[Reason For Call], tblPhoneCalls.[Line Of Business], tblPhoneCalls.Comments, tblPhoneCalls.ResourceUsed, tblPhoneCalls.[Date Entered]
FROM tblPhoneCalls;

 

[dhookom]

I don't see any date parameters in you SQL. IMO, parameter prompts are never acceptable user interface faq701-6763. Always use controls on forms for user interaction.

Duane
Hook'D on Access
MS Access MVP

 

[summer01]

I am sorry for the late reply as I was out of town without my laptop. I had removed the date that was originally there but have again put it in. From this single query I have one chart for Line of Business and another chart for Reason. Since both use the date field from this single query, the parameter causes the requests for BeginDate and EndDate to repeat itself several times. I looked at the link you provided and understand the limitations the way I am doing it so should I then set up a standard input box for the date on the main form, then put put the 2 sub forms (Charts) on the main form? I am just a bit lost on this.


SELECT tblPhoneCalls.[Supp ID], tblPhoneCalls.[Type Of Call], tblPhoneCalls.[Reason For Call], tblPhoneCalls.[Line Of Business], tblPhoneCalls.Comments, tblPhoneCalls.ResourceUsed, tblPhoneCalls.[Date Entered]
FROM tblPhoneCalls
WHERE (((tblPhoneCalls.[Date Entered]) Between [Begin Date] And [End Date]));

 

[dhookom]

then put put the 2 sub forms (Charts) on the main form

Aren't we talking about reports? I think you have the correct idea with putting the criteria in form controls.

WHERE tblPhoneCalls.[Date Entered] Between Forms!frmDates!txtBeginDate And Forms!frmDates!txtEndDate;

Duane
Hook'D on Access
MS Access MVP

 

[summer01]

You are correct. I meant to say reports not forms. Thanks for your help. It works perfectly. One last question. Is it possible that the dates can be shown on the report? I tried to put a couple of text boxes on the report for the dates, but they did not appear. Just wondering.

 

[dhookom]

You can add a text box to the report with a control source of:

="Between " & Forms!frmDates!txtBeginDate & " And " & Forms!frmDates!txtEndDate

Make sure the form stays open.

Duane
Hook'D on Access
MS Access MVP

 

[summer01]

It looks great! Thanks so much Duane.