Charts in Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to have a report that just contains a column chart to
graphically show the count of the types of complaints and the timeframe . I
have built a simple query, with a 'Date of Complaint' and 'Complaint
Category' field. The query has a date parameter prompt of 'BeginDate' and
'EndDate'. I have used the Chart Wizard when trying to create a chart, but
it doesn't graph anything, the graph shows up blank.

Can anyone help me make a simple graph based on my query.I am new to graphs
in Access and would appreciate any help. Thanks in advance.....
 
The chart will display the values from the chart control's Row Source
property. The records may be filtered by values entered into the Link
Master/Child properties or any criteria in the query.

We would need to know at least the Row Source SQL view as well as the Link
Master/Child properties.
 
The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT
(Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY
(Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date
of Complaint],"MMM 'YY")) PIVOT [Category Description];

The Link Child/Query criteria is blank. There is a parameter in the query
which asks for a begin and end date. If I take the date parameter out, then
the graph works, but when I use the parameter, then I get the following error
messages:
1) The Microsoft Jet database engine does not recognize [Start Date] as a
valid field name.
2) An error occurred while sending data to the OLE server.

I would like the date prompt included since I only want to be able to graph
a certain date range.

Thanks.....
 
IMHO, parameter prompts are never appropriate user interface. Use controls on
forms for all user input.

Crosstab queries require the explicit declaration of the data type of all
parameters. If you leave the parameter prompts then go to your query design
and select Query -> Parameters and enter something like:
[Start Date] DateTime
[End Date] DateTime

You can also enter all possible Category Description values into the Column
Headings property of the crosstab query.

BTW: did I suggest you replace parameter prompts with controls on forms ;-)


--
Duane Hookom
Microsoft Access MVP


Amber said:
The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT
(Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY
(Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date
of Complaint],"MMM 'YY")) PIVOT [Category Description];

The Link Child/Query criteria is blank. There is a parameter in the query
which asks for a begin and end date. If I take the date parameter out, then
the graph works, but when I use the parameter, then I get the following error
messages:
1) The Microsoft Jet database engine does not recognize [Start Date] as a
valid field name.
2) An error occurred while sending data to the OLE server.

I would like the date prompt included since I only want to be able to graph
a certain date range.

Thanks.....




Duane Hookom said:
The chart will display the values from the chart control's Row Source
property. The records may be filtered by values entered into the Link
Master/Child properties or any criteria in the query.

We would need to know at least the Row Source SQL view as well as the Link
Master/Child properties.
Click to expand...
Click to expand...
 
I have created an unbound form named 'Paramform' with two text boxes,
StartDate and EndDate. I have my report with just the graph in it. My
question is, what should my Report Record Source and Graph Row Source be?
I've tried changing these sources to different things, such as Between
forms!Paramform!StartDate and forms!ParamForm!EndDate. I still get error
messages when I run this.

To make the form I used the following directions:
Use a Form to enter the parameters.

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Use the same criteria in the Graph record source.

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"




Duane Hookom said:
IMHO, parameter prompts are never appropriate user interface. Use controls on
forms for all user input.

Crosstab queries require the explicit declaration of the data type of all
parameters. If you leave the parameter prompts then go to your query design
and select Query -> Parameters and enter something like:
[Start Date] DateTime
[End Date] DateTime

You can also enter all possible Category Description values into the Column
Headings property of the crosstab query.

BTW: did I suggest you replace parameter prompts with controls on forms ;-)


--
Duane Hookom
Microsoft Access MVP


Amber said:
The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT
(Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY
(Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date
of Complaint],"MMM 'YY")) PIVOT [Category Description];

The Link Child/Query criteria is blank. There is a parameter in the query
which asks for a begin and end date. If I take the date parameter out, then
the graph works, but when I use the parameter, then I get the following error
messages:
1) The Microsoft Jet database engine does not recognize [Start Date] as a
valid field name.
2) An error occurred while sending data to the OLE server.

I would like the date prompt included since I only want to be able to graph
a certain date range.

Thanks.....




Duane Hookom said:
The chart will display the values from the chart control's Row Source
property. The records may be filtered by values entered into the Link
Master/Child properties or any criteria in the query.

We would need to know at least the Row Source SQL view as well as the Link
Master/Child properties.

--
Duane Hookom
Microsoft Access MVP


:

I would like to have a report that just contains a column chart to
graphically show the count of the types of complaints and the timeframe . I
have built a simple query, with a 'Date of Complaint' and 'Complaint
Category' field. The query has a date parameter prompt of 'BeginDate' and
'EndDate'. I have used the Chart Wizard when trying to create a chart, but
it doesn't graph anything, the graph shows up blank.

Can anyone help me make a simple graph based on my query.I am new to graphs
in Access and would appreciate any help. Thanks in advance.....
Click to expand...
Click to expand...
Click to expand...
 
Try this as the Chart's Row Source. Make sure the form is open and dates are
entered.

PARAMETERS forms!Paramform!StartDate DateTime,
forms!ParamForm!EndDate DateTime;
TRANSFORM Count(*) AS [Count]
SELECT Format([Date of Complaint],"MMM 'YY")
FROM [qryGRAPH]
WHERE [Date Of Complaint] Between
forms!Paramform!StartDate and forms!ParamForm!EndDate
GROUP BY Year([Date of Complaint])*12 + Month([Date of
Complaint])-1,Format([Date
of Complaint],"MMM 'YY")
PIVOT [Category Description];

Since your report "just contains a column chart" then your report shouldn't
have any record source.

--
Duane Hookom
Microsoft Access MVP


Amber said:
I have created an unbound form named 'Paramform' with two text boxes,
StartDate and EndDate. I have my report with just the graph in it. My
question is, what should my Report Record Source and Graph Row Source be?
I've tried changing these sources to different things, such as Between
forms!Paramform!StartDate and forms!ParamForm!EndDate. I still get error
messages when I run this.

To make the form I used the following directions:
Use a Form to enter the parameters.

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Use the same criteria in the Graph record source.

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"




Duane Hookom said:
IMHO, parameter prompts are never appropriate user interface. Use controls on
forms for all user input.

Crosstab queries require the explicit declaration of the data type of all
parameters. If you leave the parameter prompts then go to your query design
and select Query -> Parameters and enter something like:
[Start Date] DateTime
[End Date] DateTime

You can also enter all possible Category Description values into the Column
Headings property of the crosstab query.

BTW: did I suggest you replace parameter prompts with controls on forms ;-)


--
Duane Hookom
Microsoft Access MVP


Amber said:
The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT
(Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY
(Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date
of Complaint],"MMM 'YY")) PIVOT [Category Description];

The Link Child/Query criteria is blank. There is a parameter in the query
which asks for a begin and end date. If I take the date parameter out, then
the graph works, but when I use the parameter, then I get the following error
messages:
1) The Microsoft Jet database engine does not recognize [Start Date] as a
valid field name.
2) An error occurred while sending data to the OLE server.

I would like the date prompt included since I only want to be able to graph
a certain date range.

Thanks.....




:

The chart will display the values from the chart control's Row Source
property. The records may be filtered by values entered into the Link
Master/Child properties or any criteria in the query.

We would need to know at least the Row Source SQL view as well as the Link
Master/Child properties.

--
Duane Hookom
Microsoft Access MVP


:

I would like to have a report that just contains a column chart to
graphically show the count of the types of complaints and the timeframe . I
have built a simple query, with a 'Date of Complaint' and 'Complaint
Category' field. The query has a date parameter prompt of 'BeginDate' and
'EndDate'. I have used the Chart Wizard when trying to create a chart, but
it doesn't graph anything, the graph shows up blank.

Can anyone help me make a simple graph based on my query.I am new to graphs
in Access and would appreciate any help. Thanks in advance.....
Click to expand...
Click to expand...
Click to expand...
 
This worked perfectly......thanks for your help!

Duane Hookom said:
Try this as the Chart's Row Source. Make sure the form is open and dates are
entered.

PARAMETERS forms!Paramform!StartDate DateTime,
forms!ParamForm!EndDate DateTime;
TRANSFORM Count(*) AS [Count]
SELECT Format([Date of Complaint],"MMM 'YY")
FROM [qryGRAPH]
WHERE [Date Of Complaint] Between
forms!Paramform!StartDate and forms!ParamForm!EndDate
GROUP BY Year([Date of Complaint])*12 + Month([Date of
Complaint])-1,Format([Date
of Complaint],"MMM 'YY")
PIVOT [Category Description];

Since your report "just contains a column chart" then your report shouldn't
have any record source.

--
Duane Hookom
Microsoft Access MVP


Amber said:
I have created an unbound form named 'Paramform' with two text boxes,
StartDate and EndDate. I have my report with just the graph in it. My
question is, what should my Report Record Source and Graph Row Source be?
I've tried changing these sources to different things, such as Between
forms!Paramform!StartDate and forms!ParamForm!EndDate. I still get error
messages when I run this.

To make the form I used the following directions:
Use a Form to enter the parameters.

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Use the same criteria in the Graph record source.

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"




Duane Hookom said:
IMHO, parameter prompts are never appropriate user interface. Use controls on
forms for all user input.

Crosstab queries require the explicit declaration of the data type of all
parameters. If you leave the parameter prompts then go to your query design
and select Query -> Parameters and enter something like:
[Start Date] DateTime
[End Date] DateTime

You can also enter all possible Category Description values into the Column
Headings property of the crosstab query.

BTW: did I suggest you replace parameter prompts with controls on forms ;-)


--
Duane Hookom
Microsoft Access MVP


:

The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT
(Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY
(Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date
of Complaint],"MMM 'YY")) PIVOT [Category Description];

The Link Child/Query criteria is blank. There is a parameter in the query
which asks for a begin and end date. If I take the date parameter out, then
the graph works, but when I use the parameter, then I get the following error
messages:
1) The Microsoft Jet database engine does not recognize [Start Date] as a
valid field name.
2) An error occurred while sending data to the OLE server.

I would like the date prompt included since I only want to be able to graph
a certain date range.

Thanks.....




:

The chart will display the values from the chart control's Row Source
property. The records may be filtered by values entered into the Link
Master/Child properties or any criteria in the query.

We would need to know at least the Row Source SQL view as well as the Link
Master/Child properties.

--
Duane Hookom
Microsoft Access MVP


:

I would like to have a report that just contains a column chart to
graphically show the count of the types of complaints and the timeframe . I
have built a simple query, with a 'Date of Complaint' and 'Complaint
Category' field. The query has a date parameter prompt of 'BeginDate' and
'EndDate'. I have used the Chart Wizard when trying to create a chart, but
it doesn't graph anything, the graph shows up blank.

Can anyone help me make a simple graph based on my query.I am new to graphs
in Access and would appreciate any help. Thanks in advance.....
Click to expand...
Click to expand...
Click to expand...
 
Back
Top