Graph Invisible if No Data

[shelby55]

Hi

I am using Access 2003. I have a report that has many graphs on it. The report itself isn't bound to a query/table but each graph has a different query source.

Sometimes there will be no data for a graph depending on the time period chosen so how do I get it so that particular graph doesn't show? Or better yet that the phrase "no data" is visible?

Thanks.

 

[dhookom]

A graph control has a row source. I expect you could use code in the On Format event of the report section to see if the Row Source returns records. If not, set the chart to invisible and display a label.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

Thanks for the suggestion but this report has many graphs within the various report sections so one graph might not have any data but another one would so I wouldn't make the whole section invisible.

 

[dhookom]

I didn't suggest you make the whole section invisible. I suggested "set the chart to invisible and display a label".

Did you want the whole section invisible?

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

Sorry but I've been off sick for a few days.

No I don't want entire section to be invisible, only the graph if no data. As stated, there are 13 graphs in this report and all have different query sets. I'm not sure how to apply what you've indicated to get this to work.

An example of the row source code for one of the graphs:

SELECT DateSerial(Year([DisDate]),Month([DisDate]),1) AS MonthEnd, Avg(FLO_Data.TotalDays) AS ALOS
FROM FLO_Data
WHERE (((FLO_Data.InstName)=[forms]![frmGraphDialog]![lstInst2]) AND ((FLO_Data.Disp) Like "2*") AND ((FLO_Data.DisDate) Between [forms]![frmGraphDialog]![txtStartDateA] And [forms]![frmGraphDialog]![txtEndDateB]))
GROUP BY DateSerial(Year([DisDate]),Month([DisDate]),1)
ORDER BY DateSerial(Year([DisDate]),Month([DisDate]),1);

Thanks.

 

[dhookom]

You might need to code in the On Format event like:

Me.chartControl.Visible = DCount("*","FLO_Data", "InstName=""" & _
   [forms]![frmGraphDialog]![lstInst2] & _
   """ AND Disp Like '2*' AND DisDate) Between #" & _
   [forms]![frmGraphDialog]![txtStartDateA] & "# And #" & _
   [forms]![frmGraphDialog]![txtEndDateB] & "#") > 0

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Thanks Duane, I'll try that and let you know.