Re: Using VBA to filter a chart

Thank you David, that got me started in the right direction.

"David Lloyd" wrote:

> Alex:
>
> Some further elaboration on the response I gave you previously.
>
> Say the query behind the chart is called qryChart and you have a second
> query called qryChart_UnFiltered, which is the query with all of your data
> unfiltered (probably what you are using currently). You could use DAO to
> reference the qryChart_UnFiltered querydef, then assign its SQL property to
> a string. You could then append a WHERE statement to the string, and then
> assign this filtered query string to the SQL string property of your
> qryChart, again through a DAO querydef reference.
>
> It would look something like the following although I am not guaranteeing
> this to be a complete solution for you, as I do not know all the specifics.
>
> Dim db as Database
> Dim qdf_Chart as QueryDef
> Dim qdf_Unfiltered as QueryDef
> Dim sql as String
>
> Set db = Currentdb
> Set qdf_Unfiltered = db.QueryDefs("qryChart_Unfiltered")
> Set qdf_Chart = db.QueryDefs("qryChart")
>
> sql = qdf_Unfiltered.SQL
>
> 'This is strFilter from your routine
> sql = sql & " WHERE " & strFilter
>
> qdf_Chart.SQL = sql
>
> If you already have a WHERE clause in your unfiltered data, you will have to
> adjust accordingly.
>
> --
> David Lloyd
> MCSD .NET
> http://LemingtonConsulting.com
>
> This response is supplied "as is" without any representations or warranties.
>
>
> "Alex" <Alex@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:79794E98-0C1F-4177-B818-8D471D94DFC3@xxxxxxxxxxxxxxxx
> I really need help with this. I've posted a few questions, but not having
> any luck. I have a report that is filtered by an input form that contains a
> multi-select list box. Normally I would use a query to filter the data then
> use that query as the report data source. Since I need users to be able to
> select more than one choice in a list box, I can't use the query. My user
> input form contains a button with the following code that works great to
> filter the report.
>
> Here's my problem: I have a chart that I need to put in the same report.
> When I run the report, the chart is not filtered the same as the report.
> How
> can I use vba to filter my chart? Is it as simple as changing ' Apply the
> filter and switch it on With Reports![LocationsReportbyStorage].Filter =
> strFilter.FilterOn = True End With End Sub? I'm totally stuck on this and
> will appreciate any help you can give. I'm very new to vba. Thank you.
>
> Private Sub cmdApplyFilter_Click()
> Dim VarItem As Variant
> Dim strStore As String
> Dim strLocation As String
> Dim datBeginDate As Date
> Dim datEndDate As Date
> Dim strFilter As String
> ' Check for Location
> If Len(Me.cmdLocation.Value & "") = 0 Then
> MsgBox "You must pick a location"
> Exit Sub
> End If
>
> 'Check for Beginning and Ending date
> If Len(Me.cmdBeginDate.Value & "") = 0 Then
> MsgBox "You must type a Beginning date"
> Exit Sub
> End If
>
> If Len(Me.cmdEndDate.Value & "") = 0 Then
> MsgBox "You must type an Ending date"
> Exit Sub
> End If
>
>
> ' Open Report
> If SysCmd(acSysCmdGetObjectState, acReport, "LocationsReportbyStorage")
> <> acObjStateOpen Then
> DoCmd.OpenReport "LocationsReportbyStorage", acViewPreview
> End If
>
> ' Build criteria string from StoreRoom listbox
> For Each VarItem In Me.cmdStoreRoom.ItemsSelected
> strStore = strStore & ",'" & Me.cmdStoreRoom.ItemData(VarItem) _
> & "'"
> Next VarItem
> If Len(strStore) = 0 Then
> strStore = "Like '*'"
> Else
> strStore = Right(strStore, Len(strStore) - 1)
> strStore = "IN(" & strStore & ")"
> End If
>
> ' Build criteria string from Location option group
> Select Case Me.cmdLocation.Value
> Case 1
> strLocation = "='1'"
> Case 2
> strLocation = "='2'"
>
> End Select
>
> 'Build Beginning & Ending date parameter
> If Not IsNull(Me.cmdBeginDate) Then
> datBeginDate = Me.cmdBeginDate
> End If
> If Not IsNull(Me.cmdEndDate) Then
> datEndDate = Me.cmdEndDate
> End If
>
> ' Build filter string
> strFilter = "[StorageRoom] " & strStore & " AND [Location] " &
> strLocation & " AND [Date] Between #" & datBeginDate & "# and #" &
> datEndDate
> & "#"
>
>
> ' Apply the filter and switch it on
> With Reports![LocationsReportbyStorage]
> .Filter = strFilter
> .FilterOn = True
> End With
> End Sub
>
>
>
.

Loading