Have a look at the properties of the chart control and you will see that it
has a Row Source property. This will show the SQL of the query that is used
for the chart's data. If you used the chart wizard to create the chart then
it will have been created by the wizard.
To change the chart data to show just one account you could do one of two
things (or probably more than 2, but these are the ones that I know of):
1. If the account number is on a control on a form that will be open, then
create a query that uses the account number from the form as a criteria.
Then base the chart on that query. You will need to requery the chart when
the account changes on the form.
2. Use VBA code to modify the SQL in the chart's row source. This isn't as
hard as it sounds as you can use the SQL produced by the wizard as a
starting point, and then add a WHERE clause to it to limit it to just the
account you want. For example, if the wizard produced the following:
SELECT [Field1],Avg([Field2]) AS [AvgOfField2] FROM [ATable] GROUP BY
[Field1];
and you wanted to restrict the chart to show only account number 3 where the
account number was stored in a field called AcNo the you would need to
change the SQL to this:
SELECT [Field1],Avg([Field2]) AS [AvgOfField2] FROM [ATable] WHERE AcNo = 3
GROUP BY [Field1];
To do this in VBA code you would use something like this:
'Start of possible code
Dim lngAcNo as long 'Assuming the account number is a long integer
Dim strSQL as string
lngAcNo = ??? 'Insert something here to return the right account number
strSQL = "SELECT [Field1],Avg([Field2]) AS [AvgOfField2] FROM [ATable] "
strSQL = strSQL & "WHERE AcNo = " & lngAcNo & " "
strSQL = strSQL & "GROUP BY [Field1];"
Me.NameOfChartControl.RowSource = strSQL
'End of code
This code could go in the AfterUpdate event of the form control that shows
the account number in which case the value of lngAcNo would come from the
control value.
tom said:
I'm having a form on which i can select a bank account for which I
want to see information.
the information comes from a table with all accounts in it.
now I want to be able to make a chart with only the selected account
in it.
how do I do that ?
filtering doesn't seem to work
