RE: Duane Hookom's Line graph by code

Hi Duane,

I got to work with your help! Much appreciated!

It is almost perfect...The only problem is that the graph does not graph the
nulls (0 values) as yours did originally. I had the same issue with chart
control. I need to graphically demostrate where there are no injuries for
that month based on a particular injury code.
Here is my string:
strSQL = "TRANSFORM CDbl(Nz(Sum(qrySearchTopValues2.IncidentCount),0)) AS
[Total Of IncidentCount] SELECT qrySearchTopValues2.SortYM,
qrySearchTopValues2.InjuryQuart From qrySearchTopValues2 WHERE
((qrySearchTopValues2.Main_code) = """ & strMainCode & """) GROUP BY
qrySearchTopValues2.SortYM, qrySearchTopValues2.InjuryQuart PIVOT
qrySearchTopValues2.Main_code"

I feel that I am in the ballpark (but with a hockey stick).

Any thoughts on how I can introduce the nulls would be deeply appreciated?

george

"Duane Hookom" wrote:

Your code in the report should look something like (assuming Main_Code is a
string):
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
DrawGraph Me.Main_Code
End Sub
Sub DrawGraph(strMainCode As String)
'
'
'
Dim strSQL as String
strSQL = "SELECT * FROM qxtbGraphMe WHERE [Main_Code]='" & _
strMainCode & "'"
Set rs = db.OpenRecordset(strSQL)
'
'

--
Duane Hookom
Microsoft Access MVP


"george 16-17" wrote:

Hi Duane,
Thanks again for the reply. The field I want to filter the graph on is
Me.Main_code. When I place it into the DrawGraph function I get an error
"wrong number of arguments or invalid propety assignment".

The report is bound to a select query where the field Me.Main_code is and
the openrecordset opens a crosstab based on the same select query.

I am really struggling with this, as I am still a newbie. I appreciate your
patience.

george

"Duane Hookom" wrote:

Assuming you have a [GroupID] field in your report, your function call might
look like:
DrawGraph(Me.GroupID)
The DrawGraph function would then used this value to filter the recordset
that is used to draw the lines.

--
Duane Hookom
Microsoft Access MVP


"george 16-17" wrote:

Hi Duane,

Thanks for replying.

I appreciate the direction and your expertise. I have been able to include
the record source and the bound text box.

A less vague question...how do I add the arugment? That is where I am
stumped. I apology in advance.

Thanks again,
george



"Duane Hookom" wrote:

You would need to include a Record Source for the report that returns the
unique group. Then add a text box bound to the group value in the detail
section.

The DrawGraph() function would need to be modified to add an argument for
the group so the OpenRecordset() method pulls only the group data.
--
Duane Hookom
Microsoft Access MVP


"george 16-17" wrote:

Greetings all,

...asking a vague question...expecting a vague answer...

I have adapted Mr Hookom's line graph by code example report from
http://www.access.hookom.net/Samples.htm. It is based on a crosstab query and
it works fine. It graphs all the data on one graph without using MS graph.
Could the code be adapted to produce a separate graph for each series of data
in a group?

Any direction would be appreciated. I can post the code if needed.
Thanks in advance,
george
.

Relevant Pages

  • RE: Duane Hookoms Line graph by code
    ... There are missing records in the select query. ... I tried the PIVOT IN clause suggestion, but it did not add in the 0s. ... "Duane Hookom" wrote: ... It is almost perfect...The only problem is that the graph does not graph the ...
    (microsoft.public.access.reports)
  • RE: Charts in Reports
    ... "Duane Hookom" wrote: ... WHERE [Date Of Complaint] Between ... I have my report with just the graph in it. ... If you leave the parameter prompts then go to your query design ...
    (microsoft.public.access.reports)
  • RE: Duane Hookoms Line graph by code
    ... Assuming you have a field in your report, ... The DrawGraph function would then used this value to filter the recordset ... "Duane Hookom" wrote: ... It graphs all the data on one graph without using MS graph. ...
    (microsoft.public.access.reports)
  • RE: Grouping w/ Graphs in Rpts
    ... A chart/graph control is much like a subreport. ... I copied the Company_Name field into the ItemDescr group header. ... I put Company_Name;ItemDescr in Link Child and the Link Master in the graph ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • RE: Duane Hookoms Line graph by code
    ... It is almost perfect...The only problem is that the graph does not graph the ... "Duane Hookom" wrote: ... The DrawGraph function would then used this value to filter the recordset ...
    (microsoft.public.access.reports)