Strange chart behaviour

[honeypot3]

Hi there

I have a report which contains a chart which sometimes displays, sometimes not and somtimes generates the message "cannot use the crosstab of a non-fixed column as a subquery". The SQL to drive the chart is:

PARAMETERS [forms]![dateselect2]![month] Text ( 255 ), [forms]![dateselect2]![year] Text ( 255 ), [forms]![dateselect2]![tomonth] Text ( 255 ), [forms]![dateselect2]![toyear] Text ( 255 ), [forms]![dateselect2]![partnername] Long, [forms]![dateselect2]![tradechoice] Long;
TRANSFORM Avg(TradePartnershipContractorGraphstouse.Resp) AS AvgOfResp
SELECT TradePartnershipContractorGraphstouse.[Question Text], TradePartnershipContractorGraphstouse.Target
FROM TradePartnershipContractorGraphstouse
GROUP BY TradePartnershipContractorGraphstouse.QuestionID, TradePartnershipContractorGraphstouse.[Question Text], TradePartnershipContractorGraphstouse.Target
ORDER BY TradePartnershipContractorGraphstouse.QuestionID
PIVOT TradePartnershipContractorGraphstouse.[Partnership Name];

The strange thing is that when the report is first run, it does not display and sometimes throws the error message mentioned above but if I go into the design of the report and into the SQL view but do not do anything else (no changes made) and then preview the chart again it displays no problems! However each time the report is opened from scratch the graph fails to display? Any ideas?

 

[dhookom]

ACG Soft has some tips for graphs in reports at

http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm.

You may need to requery the graph control.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[honeypot3]

Thanks I will have a look