MsChart Help

[dlpastel]

I am trying to graph an average lifetime of a pay customer in our Clients database
The fields I am using are:
StartDate (day the person joined)
LastUsed (last time the person used the system)
MemberType (Payline or Freeline customer)

I want to chart a average that spans a specifed date range and see the columns separated in weeks. If I try to do it in days, the chart gets to smashed up.
Here is what I have so far but it does not seem to work.

Private Sub ApcGraph()
Dim StrSql As String
On Error GoTo ProcErr
Set rsApc = New ADODB.Recordset
StrSql = "SELECT Avg((DateDiff('d',CDate([startdate]),CDate([lastused])))) AS AvgLife, Clients.StartDate " & _
"From Clients " & _
"Where (((CVDate([StartDate])) >= '#7/1/2006#') " & _
"And ((CVDate([LastUsed])) <= '#7/31/2006#') " & _
"And ((Clients.MemberType) = 'PayLine')) " & _
"GROUP BY Clients.StartDate " & _
"Order BY Clients.StartDate; "

Debug.Print StrSql

rsApc.Open StrSql, dbApc, adOpenKeyset, adLockBatchOptimistic
Debug.Print rsApc.RecordCount
Set MSChart1.DataSource = rsApc
DoEvents
ProcErr:
Debug.Print Err.Description
Resume Next
End Sub

Any help would be greatly appreciated

Thanks,
Dan

 

[rds747]

If you run the query in SQL Server or whatever db you're using, does it display the correct data?