Chart won't display my data, only sample data

[chiefman]

I have created a report with a chart. I ran through the chart wizard and had everything set up like I wanted. Even the chart preview looked exactly like I wanted, however, when I do a print preview on the report I get the sample Microsoft data instead of the chart I created. How do I get this to work? Thanks.

 

[dhookom]

Are you doing a sample format preview? I usually open the datasheet view of the chart's row source and paste it into the datasheet view of the chart control.

ACG Soft has some graph tips at

http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

Thanks, I'll check it out. You've been a big help.

 

[chiefman]

I included the code they had and now it only displays the title and the outline. Is there any way to make a chart other than with the chart wizard?

 

[dhookom]

If you aren't showing data, then check the Link Master and Child properties. You should only have values in these properties if you want to filter the chart based on a field from the main report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

Everything looks ok there. I'm going to run through the chart wizard again and see if I can't get it to display correctly.

 

[dhookom]

Most of the time, I will create a chart on a form first. This allows you to view the form in Form View and double-click the chart control to open MS Graph. You can then make changes and close MS Graph to get back to your form. When finished designing the graph, paste it in your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

Ok, I'll try that.

 

[chiefman]

I ran through creating it on a form and pasting into the report and I have it set up ok. When I go to the query builder in datasheet mode it comes out perfectly. However when I go to the report in design view it shows all the data, even the stuff I am trying to filter out. It is supposed to be a count of all the cases where "Cancelled_Cases" is set to "Yes" and the month and the year match the current month and year. What it is giving me is a count of the "No" records as well as the "Yes" records regardless of the date. This is the query I am using:

SELECT Count(*) AS [Count] FROM PerMonInput WHERE (((Format([Date],"mm/yyyy"))=Format(Now(),"mm/yyyy")) AND ((PerMonInput.Cancelled_Cases)="Yes"));

It counts correctly in query datasheet view. I read somewhere that sometimes you have to put a loop in the "On Format" event of the detail section to basically just waste time until it can finish formatting and that seemed to work to some extent when I tried the report before (it would actually give me data from my table instead of the sample data), but it hasn't made any difference with this one. I had it set to run through 240,000 times and it still came up with unfiltered data.

 

[dhookom]

Is that the SQL view of your chart's Row Source property? That particular SQL should show only one value.

Is the Cancelled_Cases field a Yes/No or Text data type?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

Yes, that's for the Row Source. Yeah, it should only show the "Yes" values, and it does in the data sheet. The data type is text.

 

[dhookom]

The ACG Soft web page suggested using code to Requery to chart object. Did you do that?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

I tried one form of it. I'll have to go back to their example and see what they have.

 

[chiefman]

When I try what they have my graph disappears and all I get are the title and the border, even if I take that code out it doesn't come back.

 

[dhookom]

So, you are stating that a Row Source of:

SELECT Count(*) AS [Count] FROM PerMonInput WHERE (((Format([Date],"mm/yyyy"))=Format(Now(),"mm/yyyy")) AND ((PerMonInput.Cancelled_Cases)="Yes"));

will show one record. Also, there is no value in the Link Master and Link Child properties of the chart control. Is this correct?

Do you have any code in the section of the report containing the chart control? What is PerMonInput? If this is a query, can you provide the SQL view?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

It actually was showing 2 values on the X-Axis. "Yes" and "No" and was displaying the count for both. Then I added the extra criteria and it began showing just the count for "yes". However it would never take the date criteria into account, even though in query builder that "Row source" pulled all the correct information.

Correct, there is no value in either the Link Master or Link Child fields.

The code in the table section is very basic. It may not even be neccessary, but I read that sometimes it helped to have a loop in order to refresh the graph. Here's the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim x As Long

For x = 1 To 60000
OLEUnbound0.Requery
Next x

End Sub

PerMonInput is the table that the data is being pulled from. Would I be better off doing a query here?

 

[chiefman]

I may have figured something out. Let me play around with it a little bit and I'll let you know. Thanks again for your help.

 

[dhookom]

You should only need to requery the chart control once.

Also, I don't like a field named date since date is the name of a function. The following is ALWAYS true:
Format(Date(),"mm/yyyy")=Format(Now(),"mm/yyyy")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

Ok, I have it working for my overall data, now I just have to figure out how to break it down by categories.

That is correct about the format function, I didn't even think about changing the name. However, I am using Format([Date]...) instead of Format(Date()...) and it's working, I just have to be careful not to type the wrong statement.