> I expect you are getting 0s in all of your data but are missing either
> records or columns. Is this correct? If so, you need to use a LEFT or RIGHT
> JOIN to get all the SortYM and InjuryQuart values or include the column
> headings values in your PIVOT clause ie:
> PIVOT qrySearchTopValues2.Main_Code IN (....).
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "george 16-17" wrote:
>
> > 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