chart does not reflect parameter querry

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

OI am not solving this problem easily.

I wish to generate a report based on a querry and various parameters
set in a form. The querry "basicreportquerry" returns allk the
records in the dataset. I use a form with various listboxes and
comboboxes to enter criteria for several of the fields. Then I use the
following VBA to generate the sql statement.

Dim varItem As Variant
' the six list boxes which correspond to the report filters.
Dim strCCN As String
Dim strdisease As String
Dim strtopleveldisease As String
Dim strconsultant As String
Dim strPCT As String
Dim strReport As String


Dim strcaseload As String 'this variable stors the result of the
active caseload option group.

Dim strFilter As String 'this stores the sql phrase made fromthe above
variables




' for the CCN listbox
For Each varItem In Me.CCN.ItemsSelected
strCCN = strCCN & "," & Me.CCN.ItemData(varItem) _
& ""
Next varItem

If Len(strCCN) = 0 Then
strCCN = "Like '*'"
Else
strCCN = Right(strCCN, Len(strCCN) - 1)
strCCN = "IN(" & strCCN & ")"
End If

' for the Consultant list box
For Each varItem In Me.Consultant.ItemsSelected
strconsultant = strconsultant & "," &
Me.Consultant.ItemData(varItem) _
& ""
Next varItem

If Len(strconsultant) = 0 Then
strconsultant = "Like '*'"
Else
strconsultant = Right(strconsultant, Len(strconsultant) - 1)
strconsultant = "IN(" & strconsultant & ")"
End If

'for PCT list box
For Each varItem In Me.PCT.ItemsSelected
strPCT = strPCT & "," & Me.PCT.ItemData(varItem) _
& ""
Next varItem

If Len(strPCT) = 0 Then
strPCT = "Like '*'"
Else
strPCT = Right(strPCT, Len(strPCT) - 1)
strPCT = "IN(" & strPCT & ")"
End If

'for disease box.

For Each varItem In Me.disease.ItemsSelected
strdisease = strdisease & "," & Me.disease.ItemData(varItem) _
& ""
Next varItem

If Len(strdisease) = 0 Then
strdisease = "Like '*'"
Else
strdisease = Right(strdisease, Len(strdisease) - 1)
strdisease = "IN(" & strdisease & ")"
End If

'for top level disease box.

For Each varItem In Me.ToplevelDisease.ItemsSelected
strtopleveldisease = strtopleveldisease & "," &
Me.ToplevelDisease.ItemData(varItem) _
& ""
Next varItem

If Len(strtopleveldisease) = 0 Then
strtopleveldisease = "Like '*'"
Else
strtopleveldisease = Right(strtopleveldisease,
Len(strtopleveldisease) - 1)
strtopleveldisease = "IN(" & strtopleveldisease & ")"
End If

' for ioption group activecaseload

Select Case Me.activecaseload.Value
Case 1
strcaseload = "='Y'"
Case 2
strcaseload = "='N'"
Case 3
strcaseload = "Like '*'"
End Select

' now select the correct report style.

Select Case Me.reportname.Value
Case 1
strReport = "reportsummary"
Case 2
strReport = "reportsummary2"
Case 3
strReport = "reportsummary3"
Case 4
strReport = "reportsummary4"
Case 5
strReport = "reportsummary5"
Case 6
strReport = "reportsummary6_graphsex"
End Select

If SysCmd(acSysCmdGetObjectState, acReport, strReport) <>
acObjStateOpen Then
DoCmd.OpenReport strReport, acViewPreview
End If

' ************************
' now make the SQL statement
' ****************************

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [PCT-ID] " & strPCT & _
" AND [DiseaseID] " & strdisease & _
" AND [toplevelDiseaseID] " & strtopleveldisease & _
" AND [Active] " & strcaseload

' Apply the filter and switch it on
With Reports(strReport)
.Filter = strFilter
.FilterOn = True
' .txtReportTitle.Value = "Caseload Report - "
End With

As you can see the form is quite complex and allows the useer to
manipulate the querry in some depth. The problem is with the report
reportsummary6_graphsex which simply shows how many males and females
are in the querry. Note I don't allow the person to filter on male
female. What I want the graph to show is the ratio of male V female in
the sub querry. However the graph only shows all the males and
females. i.e. it does not apply the sql to the basic querry.

How do I make the graph change with each request?

.

Relevant Pages

  • Show Chart Total in Report Header
    ... A button on my user input form runs the following code, which opens a report, ... Dim qdf_Chart As DAO.QueryDef ... Dim sql1 As String ... For Each VarItem In Me.cmdGender.ItemsSelected ...
    (microsoft.public.access.modulesdaovba)
  • Re: chart does not reflect parameter querry
    ... The report's record source and filter don't apply to the chart's row source ... I wish to generate a report based on a querry and various parameters ... Dim varItem As Variant ... For Each varItem In Me.CCN.ItemsSelected ...
    (microsoft.public.access.reports)
  • Re: Code to Dynamically Filter a Report; Not Working
    ... report open. ... Dim strCustomer As String ... Dim strExecBroker As String ... For Each varItem In Me.lstCustomer.ItemsSelected ...
    (microsoft.public.access.modulesdaovba)
  • Re: Produce a report from selected items in a listbox on a form
    ... Dale thanks for that, its great. ... > a separate report for each of the selected items, ... > Dim intBound as integer ... > For Each varitem In Me.lstYourListName.ItemsSelected ...
    (microsoft.public.access.formscoding)
  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.dotnet.languages.vb)