Help with Code to set ChartTitle Property

[Kirsti]

Hi,

I have this code in my Report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.MyGraph.ColumnCount) Then
Me.MyGraph.ChartTitle.Text = "No Data to Display for Previous Month"
Else
Me.MyGraph.ChartTitle.Text = "MyReportName - " & Format(DateAdd("m", -1, Now()), "mmmm")


End If
End Sub

It used to work, and no longer does. I have tried changing "IsNull" to "=0" or ">= 1" with no success. I can get the chart title to display both "No Data to Display for Previous Month", but then it displays that chart title whether or not there is data in the chart. Same goes for displaying "MyReportName".

So, I am guessing that the ColumnCount property is not working for some reason. I am wondering if there is a way to count the rows in the query the report is based on instead?

Any help gratefully received.

Kirsti

[Kirsti]

Ok, I now have the code below. But the query the report is based on is a Paramater Query, and when I open the Report I get the Error: Too few paramaters, expected 2. The parameters are: Forms!frmMyForm!myfield1 and Forms!frmMyForm!myfield2 - but I don't know how to add this into the code.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database, rs As Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select * From qryMyQuery"
Set rs = db.OpenRecordset(strSQL)
If RecordCout = 0 Then
Me.MyGraph.ChartTitle.Text = "No Data to Display for Previous Month"
Else
Me.MyGraph.ChartTitle.Text = "MyReportName - " & Format(DateAdd("m", -1, Now()), "mmmm")
End If
End Sub

Kirsti

[June7]

Maybe don't need to refer to the form parameters.

Not sure about recordset syntax and the If line should be:
If rs.RecordCount = 0


Could try this instead.

If DCount("*", "qryMyQuery") = 0 Then
Me.MyGraph.ChartTitle.Text = "No Data to Display for Previous Month"
Else
Me.MyGraph.ChartTitle.Text = "MyReportName - " & Format(DateAdd("m", -1, Now()), "mmmm")
End If

[Kirsti]

Ok. Sorry June I have been wasting your time. Actually, the query will ALWAYS return rows because I have placeholders in the query to fill the legend. No wonder my code wasn't working.
So, my table looks like this:


So if there is going to be data in the chart, the Days_VAC and Type fields will always have data, and the Units field will always be >0. Now I've tried this code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([Queries]![MyQuery]![MyField]) Then
Me.MyGraph.ChartTitle.Text = "No Data to Display for Previous Month"
Else
Me.MyGraph.ChartTitle.Text = "MyReportName - " & Format(DateAdd("m", -1, Now()), "mmmm")
End If
End Sub


But I'm getting an error. I imagine I need to somehow code it to say if that field is null for every row... But I don't know how to do that.

[June7]

Try:

If IsNull(DLookup("Units","qryMyQuery","Units<>0")) Then

[Kirsti]

Thank you June! I've tested both with data and without and FINALLY get the right result!

Thank you thank you thank you thank you.