Apply filter on Chart

[dbdb]

Hi,

i create a chart in ms access based on my query, then i want my chart when is it open is only show value based on my criteria.

i'll try to used it in the properties apply filter using the expression, it didn't work. the chart still viewing all data.
and i used the event "on open" and used "applyfilter" command
then it shown an error.

"that the report isn't bound with the query"

yes, i check it on the properties there are no query that bound to the chart.
so, how can i apply filter for my chart?

thank you.

[NeoPa]

Hi.

That's not very clear I'm afraid. Perhaps it might help if you posted the code you use to create your chart. Is the chart an object on a report?

[dbdb]

my chart is on the report.
i build it using chart wizrd in the report.

there are no code.

[NeoPa]

You don't start your report with any code? You're sure?

How about the design of the report then? There must be a Record Source.

What's the chart built on data-wise?

I can't work with absolutely no information. A crumb or two is a bare minimum.

[dbdb]

mm so do i confused when i found no record source on it.
here is the story.

first, i run the report wizard then i choosed to build a chart based on a query.
then the wizard done, and my chart shown on the monitor.

then i want to view the report only the data that i want, but i don't want to use any criteria expression on my query, i want to apply filter on my report chart.

[NeoPa]

@dbdb
What is the query the chart is built upon then? I need to see the SQL of it (rather than the name).
@dbdb
You may need to provide the SQL for the report too.

You should also explain how you, as a user of the database, cause the report to run. Do you run it directly from the Database window?

[ADezii]

I agree with NeoPa in that you are giving us very little to work with, but first let's straighten out a few points:

1. I'm making the assumption that the Report does not have a Record Source.
2. The Chart, actually the Object Frame containing the Chart, does have a Row Source which will populate it.
3. You can Open the Report in Design View (Hidden), modify the Row Source of the Object Frame containing the Chart, then re-open it, as in:
   

   DoCmd.OpenReport "Report1", acViewDesign, , , acHidden
   Reports!Report1![OLEUnbound0].RowSource = "TRANSFORM Sum(tblChart.Quantity) AS SumOfQuantity " & _
                                             "SELECT tblChart.Fruit FROM tblChart WHERE " & _
                                             "tblChart.Month='April'GROUP BY tblChart.Fruit PIVOT tblChart.Month;"
   DoCmd.OpenReport "Report1", acViewPreview

4. Any questions, feel free to ask.

[dbdb]

mmm finnaly....
sorry guys, sometimes we never realize a small miss perception we get.
yes i found the recordsource.

yesterday i always getting confused to find where is the record source in the report properties.
but then today, i realize that the recourdsource is place in the chart properties.

OMG...
sorry for this litlle mistake.
then i can view my chart based on the data that i want.

thank you for your help....

nice to joining in this group.

[NeoPa]

It's nice to have you.

I should add one thing that may prove helpful.

Normally, Access objects have a property, separate from the RecordSource property, that enables you to specify some form of filtering.

This allows you to separate the filtering from the basic fundamentals of the data. This is often helpful and simplifies matters. Especially when working in code.

To keep things simple, I suggest you look for, and work with, this property instead of changing the Record Source all the time.

I'm afraid I don't know what the details are for Chart objects, but a quick look through the properties should do it for you.

There is also a way of passing this as a parameter when opening objects in code for forms and reports. See if there is anything available for charts.

[ADezii]

@NeoPa
Hello NeoPa, a Chart in Access is an Embedded/Linked OLE Object with the OLE Server being Microsoft Graph. This being said, I do not think that you can 'Filter' it in the traditional sense of the word, but I could be wrong. What you can do is specify Criteria in the underlying SQL, via the Query Grid, by dropping the Field into the Grid, set the Total Row = Where, then specifying your criteria.

[NeoPa]

Thanks ADezii. I'm a little hazy on this as I've never played with it myself.

As for updating the underlying SQL, I would see this as amending the Record Source directly. A good plan if there is no option for specifying a filter on the fly, but it does have drawbacks when compared with that option.

1. It counts as updating the object. Immediately putting the database into exclusive mode, or failing to update if that's not possible.
2. It leaves the object different from how it was found, and therefore not usable in a multi-user environment. Attempts to replace the original will not really fix this problem in a multi-user environment.

At the end of the day though, it's better than not having access to it at all.