Filtering with Graphs 2

[Maquis]

Hi, I hope someone can help with this. I don't know if it is a simple question or not.

I'm developing a database for a set of users and for every report in the database they have requested an accompanying graph. I have never used charts or graphs in Access before, so I'm just now learning how to design one.

I've gotten a little bit figured out, but I'm stuck on the filtering part. For the "normal" report, the user fills out a bunch of combo boxes on a form and clicks a command button. I have VBA code on the form which builds and sends the appropiate filtering criteria to the report when it opens, like so:

DoCmd.OpenReport "report1", acPreview, , Criteria
DoCmd.OpenReport "report1 - Graph", acPreview, , Criteria

The report opens and displays the appropiate records, however the graph displays all the records, regardless of the criteria sent to it.

Any ideas? Like I said, I'm extremely new at creating graphs, so I hoping it's an obvious fix. Maq
<insert witty signature here>

 

[zyabc]

Did u find out how to go abt for it. Since i am also looking for the similar solution. Pls help me out.

 

[dhookom]

The Row Source of the Graph control is similar to the Record Source of a report. The graph control doesn't have a filter property so all criteria for the graph must be established in the Row Source. There is an exception to this since you can use the Link Master/Child properties to filter graph records much like you would use these properties to limit the records displayed on a subreport.

Duane
MS Access MVP

 

[Maquis]

Well, unfortunately I did not find an answer. I kept working on it and if I can remember correctly, you can't have a filter on a graph. I think I was planning on just building a temp table or a temp query which would contain just the data needed for the chart and setting that as the chart's record source. (This could be a problem in a multi-user environment though).

However, shortly after I posted this my users decided that they didn't need graphs after all. Actually they were already creating these graphs in Excel and even after I completed the database with graphs they were still planning on keeping and using the Excel graphs. So I subtly hinted that perhaps we didn't need to keep pulling our hair out struggling to create something that they already had!!

Oh, I see Duane beat me in while I was writing this. Yep, I was right. Sorry no filters. (Gotta learn to write faster)

Maq
<insert witty signature here>

 

[zyabc]

Thanks for replying.... I want to create a graph based on Report. In the rowsource it is working for static data but not for dynamic data. Can you pls tell me how to go abt for that.

Here is the sample Row Source i am working on...


SELECT (Format([EntryDt],&quot;Short Date&quot) AS Expr1,Sum([Entry Date Report Query].Account_No) AS SumOfAccount_No FROM [Entry Date Report Query] WHERE ((([Entry Date Report Query].EntryDt)=#[forms]![graph]![fromdt]#)) GROUP BY (Format([EntryDt],&quot;Short Date&quot),(Int([EntryDt]));

When i pasted this query it displaying blank. Any idea why is it so. Pls help me out.

 

[Maquis]

I don't think you can reference a form in the Where clause of a graph. (Don't ask me why, I don't know). Can you change your select query to a make table query instead and just set the graph's rowsource to be the resulting table.

I know it's probably not the best solution or even one that 'expert programmers' would approve of, but if it gets you up and running....

Maq
<insert witty signature here>

 

[zyabc]

Thanks for helping me out. I would like to copy the data from one table to dummy table. How to do it in VBA?

 

[Maquis]

Well, if your dummy table already exists just run a Delete query to clear it out and then run an append query to fill it up again. If it doesn't exist, then use a Make table query.

To do this in VBA, just use the docmd.RunSql command. The easiest way is to go ahead and create a quick query which contains all the logic you need and then go to SQL View and copy the syntax that it generated. Then just cut and paste that syntax into your VBA module.

Maq
<insert witty signature here>

 

[GingerR]

yes you can reference a form in the criteria of a graph recordsource

if your query runs fine and returns the records you want to chart, but the data is not showing up in the chart, try this:

in the query design, right-click up in the grey area above the query grid.
choose PARAMETERS from the shortcut menu.
in there, put any parameters you are passing ([forms]![graph]![fromdt]) and their data type.

see if that works.

 

[zyabc]

I would like to first thank you all for quick response & trying to help me out.

GingerR:

SELECT (Format([EntryDt],&quot;Short Date&quot) AS Expr1,Sum([Entry Date Report Query].Account_No) AS SumOfAccount_No FROM [Entry Date Report Query] WHERE ((([Entry Date Report Query].EntryDt)=#[forms]![graph]![fromdt]#)) GROUP BY (Format([EntryDt],&quot;Short Date&quot),(Int([EntryDt]));

In the above query, if there is #26/07/2003# instead of
#[forms]![graph]![fromdt]# it is working fine. I think I should change the criteria to the form value. How to do go abt for that. Can you please tell me in detail in which query design should change the parameter. If you can tell me in detail I would be very grateful.

Once again Thanks to all of you.

 

[dhookom]

GingerR is correct. Make sure the form is open and the query returns records. I don't know why you would ever want to Sum and Account_No. I would expect an Account_No field to be some value that doesn't make any sense to sum.

If this is a text field, it could be causing your problem.

Duane
MS Access MVP

 

[GingerR]

is your form called GRAPH open, and a date typed into a text box called fromdt?

 

[zyabc]

In the form, the &quot;from date&quot; & &quot;to date&quot; is got from the user. When the &quot;Submit button&quot; is clicked I open the report with the help of &quot;where condition&quot;. I don't know how to set the criteria for chart in the rowsource which accepts &quot;form values&quot;. I came to know that the rowsource cannot be set in the VB code it can be only set in the design mode.

Any ideas? Pls help me out.

 

[dhookom]

The chart's Row Source is a query. You replied earlier with the SQL (modified)
SELECT (Format([EntryDt],&quot;Short Date&quot) AS SomeDate,
Sum(Account_No) AS SumOfAccount_No
FROM [Entry Date Report Query]
WHERE EntryDt=[forms]![graph]![fromdt]
GROUP BY (Format([EntryDt],&quot;Short Date&quot)
ORDER BY [EntryDt];

Do you really have records in your report or do you just want to display the graph?

Duane
MS Access MVP

 

[zyabc]

Thanks very much Duane it works perfectly well. Thanks to all of you for your quick response & helping me out.

Thanks.