The Chart Row Source statement is:
TRANSFORM Sum([qryStrongly Agree Chart].[Count Of Survey]) AS [SumOfCount Of
Survey]
SELECT [qryStrongly Agree Chart].QACode AS Expr1
FROM [qryStrongly Agree Chart]
GROUP BY [qryStrongly Agree Chart].QACode
PIVOT [qryStrongly Agree Chart].YrQtr;
The Row Source of the chart was created by the report/chart creation wizard.
All references to the controls on the form are in the stored query
(qryStrongly Agree Chart) which is collecting and summarizing the data. One
of those controls is a MultiList box, the other is a combobox control.
The statement for the stored query is:
SELECT DISTINCTROW
Year(Survey.SurveyDate)*4+DatePart('q',Survey.SurveyDate)-1 AS Expr1,
TargetAreas.TargetParent, TargetAreas.TargetDesc, [SurveyYear] & "Q" &
[SurveyQuarter] AS YrQtr, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode])
AS QACode, Format$(Survey.SurveyDate,'\Qq yyyy') AS [SurveyDate By Quarter],
Survey.SurveyArea, Survey.SurveyQuarter, Survey.SurveyYear, Survey.QuestCode,
Survey.AnswerCode, Count(*) AS [Count Of Survey]
FROM TargetAreas INNER JOIN Survey ON TargetAreas.TargetNo = Survey.SurveyArea
GROUP BY Year(Survey.SurveyDate)*4+DatePart('q',Survey.SurveyDate)-1,
TargetAreas.TargetParent, TargetAreas.TargetDesc, [SurveyYear] & "Q" &
[SurveyQuarter], "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode]),
Format$(Survey.SurveyDate,'\Qq yyyy'), Survey.SurveyArea,
Survey.SurveyQuarter, Survey.SurveyYear, Survey.QuestCode, Survey.AnswerCode
HAVING (((TargetAreas.TargetParent)=[Forms].[Chart Report
Criteria].[USurvArea]) AND ((Survey.AnswerCode)=4) AND
((Year([Survey].[SurveyDate])*4+DatePart('q',[Survey].[SurveyDate])-1)>=(Year([forms].[Chart Report Criteria].[USurvYr])*4+(DatePart('q',Now())-1)-1)-7))
ORDER BY [SurveyYear] & "Q" & [SurveyQuarter], Survey.SurveyArea,
Survey.QuestCode, Survey.AnswerCode;
If I run the stored query "manually", I get the prompting for both of the
form controls and the query runs fine. When I run the chart "manually", I
get error messages that the Microsoft Jet database engine does not recognize
the form controls as valid field names. When I click OK on that message,
another pops up about sending data to the OLE Server. When I click OK there,
I get a blank chart, but I do get page headings. I get the same results when
running thru code.
Hopefully, this will help.
Thanks.
:
Tell us about the Row Source of your chart control. Does it have a reference
to a control on a form? Is the Row Source a crosstab query? Have you set any
Query->Parameters in your source?
--
Duane Hookom
Microsoft Access MVP
:
I am using a form to provide the parameters to the query as your website
suggests. When I mentioned that the two values were prompted, that was
during my testing/debugging and running the query "manually" rather than via
the form and code process. Ultimately, the end-user will be using the form
to provide the values.
Essentially, my flow is to use the form to provide the values to a stored
query which pulls and summarizes the data, then feeds the chart report. I
use this process with other standard reports, but for some reason it does not
seem to work with a chart. When used with a chart, I get the error I
mentioned earlier.
Thanks.
:
"it prompts for the two values" IMHO don't ever use parameter prompts
http://www.tek-tips.com/faqs.cfm?fid=6763.
I don't know how you are using the multi-select list box in your chart row
source.
--
Duane Hookom
Microsoft Access MVP
:
I have a similar problem. In my case, the multilist box is combined with
another field and is used in a stored query. The query then drives the chart
report.
When I run the stored query manually, it prompts for the two values and runs
fine. When I run the chart/report manually, I get an error the the field(s)
from the multilist field is not recognized. I suspect that is why running
the code results in nothing.
I do something nearly identical in a standard (non-chart) report and that
works fine. Is there something unique about a chart that the logic is
different?
Thanks.
:
I expect you are using code that loops through the selected items from the
list box to build a where condition for your report. A chart control is
similar to a subreport in that you can't easily apply a filter to its Row
Source property other than Link Master/Child.
I usually base the Chart on a saved query. This allows me to use a little
DAO code to update the saved query's SQL property prior to opening the
report.
Assuming your chart's Row Source is based on the query "qtotDeptEmps".
Dim strSQL as String
strSQL = "SELECT Dept, Count(EmployeeID) As NumEmps " & _
"FROM tblEmployees " & _
"WHERE " & strWhereFromListBoxItems & " " & _
"GROUP BY Dept " & _
"ORDER BY Dept;"
CurrentDb.QueryDefs("qtotDeptEmps").SQL = strSQL
--
Duane Hookom
Microsoft Access MVP
:
I have created a multi list box for a report and I can get the report to run
with the items selected in the multi list box. I cannot however get the code
to do the same thing for a chart. Is there something basic I am missing or
will the code need to be totally re-written? I am extremely new to working
with Visual Basic.
