ACCESS CHART REPORTS

[pittypat]

I have generated a chart report in access that plots a line graph of numbers of defect for specific defect types. I generate the report from a query that sorts out the top five hitters and prompts me for a date range before printing the chart. The chart prints great but I have one problem. I can't figure out how to print the date range on the chart. Unlike standard reports you can't add unbound fields to a chart report and have it prompt you for a customer header. Any help would be appreciated.

 

[ghubbell]

Hi PittiPat,
In cases as this you could try starting your report from a little "report dialog" form, with "StartDate" and "EndDate" fields, and a Print or Preview command button. Your query criteria becomes something like:

Between Forms![NameofForm]![StartDate] And Forms![NameofForm]![EndDate]

On your report you can add two text fields who's control source's would respectively be =Forms![NameofForm]![StartDate],=Forms![NameofForm]![EndDate] (play with the formatting until it suites your tastes...

Another little trick is to set the "Default Value" of the EndDate on your form to Date() (that's today) if you regularly query to the current day, and still another is to make your little form invisible when the report opens (report "On Open" event in VB: select "Event Procedure", click "..." beside):

Forms![NameofForm].visible = False

and visible again in the reports "On Close" event in VB:

Forms![NameofForm].visible = True

This works pretty well for me!
Gord
ghubbell@total.net

 

[pittypat]

Thanks for the quick reply. I went through the steps you recommended and it works great with a standard report but I can't seem to get it to work when I generate a graph using Chart Wizard from the report module. When I go to preview the chart it says the query command "Between Form![Name of form]!...etc" is not a valid command. I can generate a standard report from the same query and it works fine. I even tried building a standard report and inserting a chart and it gives me the same error. I even tried inserting an unbound text field to the report so that it would prompt me again for the date range and insert it into the report. It works fine with a standard report but with a chart it just show an error. I'm sure I am overlooking something obvious. Thanks again and all help would be appreciated.

Pat

 

[ghubbell]

True, true... this will be an issue as in reality your chart is a "cross tab query" and unless the criteria is a row or column heading, we're out of luck...almost...

It's a little more work but the results should be there in the end:
You will need to build a table to contain the data based on the fields you choose in your query. No data inserted.

One Append query that loads the table based on your criteria queries results. No biggy, basically a flip of the query type switch.

You'll also need a Delete query that will clean out the table before each use. That one's a breeze as it's just a matter of taking the TableName* ,putting it in the grid and switching the query type to delete.

Now your chart: It's record source has to be your new table.
You might be best to try making a fresh chart (with a wizard) based off of your new tables data and review it to make sure the results are to your liking. If something is missing just back-track a little and add the info you need through the queries.

When you click the Print/Preview button your code should look something like this:

On Error Goto Err1 'in case of boo boos (technical term)

Forms![NameofForm].visible = False
DoCmd.SetWarnings False 'I don't want warnings
DoCmd.OpenQuery "NameOfDeleteQuery" 'cleans out the table
DoCmd.OpenQuery "NameofAppendQuery" 'adds in the new data
Docmd.OpenReport '.....as before.

Exit1:
DoCmd.SetWarnings True 'turn 'em back on
Exit Sub 'leave this place!

Err1:
MsgBox Err.Number & " "& Err.Description
Resume Exit1 'go back to exit1 after telling me the problem

Really not as bad as it looks and I'm sure you can do it! Keep us posted or write back if you get stuck.
Gord
ghubbell@total.net