Chart problems

[Opal]

I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see. The query is as follows:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

And works fine. When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.

However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?

 

[Duane Hookom]

The Chart control has Link Master/Child properties. Your's may be set wrong.

 

[Opal]

The Chart control has Link Master/Child properties. Your's may be set wrong.

--
Duane Hookom
Microsoft Access MVP








- Show quoted text -

Sorry Duane, I am new to charting in Access. I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:

"Can't build a link between unbound forms."

Can you direct me on how to overcome this error?

 

[Opal]

Sorry Duane, I am new to charting in Access.  I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:

"Can't build a link between unbound forms."

Can you direct me on how to overcome this error?- Hide quoted text -

- Show quoted text -

Ah, further research reveals this to be a common problem
in Access. So I manually put in the fields and I am
still getting a blank chart....?

 

[Duane Hookom]

Can you provide some information about your report's record source and the
chart row source?

Did you see any chart display when the link properties were blank?

 

[Opal]

Can you provide some information about your report's record source and the
chart row source?

Did you see any chart display when the link properties were blank?

--
Duane Hookom
Microsoft Access MVP






- Show quoted text -

Hi Duane,

Yes, I did see the chart display if I do not have the the following
statement in the query:

HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

What information did you want for the record source?

This is the SQL for the Chart:

TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;

 

[Duane Hookom]

If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate Date/Time
Forms!frmSearchrpts!txtStartDate Date/Time
--
Duane Hookom
Microsoft Access MVP

Can you provide some information about your report's record source and the
chart row source?

Did you see any chart display when the link properties were blank?

--
Duane Hookom
Microsoft Access MVP
















- Show quoted text -

Hi Duane,

Yes, I did see the chart display if I do not have the the following
statement in the query:

HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

What information did you want for the record source?

This is the SQL for the Chart:

TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;

 

[Opal]

If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate     Date/Time
Forms!frmSearchrpts!txtStartDate   Date/Time
--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Yes, I did see the chart display if I do not have the the following
statement in the query:

HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

What  information did you want for the record source?

This is the SQL for the Chart:

TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -

- Show quoted text -

? But that SQL statement is from the chart wizard.

The SQL for the query from which the chart is based is a
select query:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

 

[Duane Hookom]

I wasn't kidding when I suggested you needed to set the data types of the
parameters. Did you try it?

--
Duane Hookom
Microsoft Access MVP

If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate Date/Time
Forms!frmSearchrpts!txtStartDate Date/Time
--
Duane Hookom
Microsoft Access MVP

On Aug 15, 12:41 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide some information about your report's record source and the
chart row source?

Did you see any chart display when the link properties were blank?

:
On Aug 14, 9:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:

The Chart control has Link Master/Child properties. Your's may be set wrong.

:
I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see. The query is as follows:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

And works fine. When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.

However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?- Hide quoted text -

- Show quoted text -

Sorry Duane, I am new to charting in Access. I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:

"Can't build a link between unbound forms."

Can you direct me on how to overcome this error?- Hide quoted text -

- Show quoted text -

Ah, further research reveals this to be a common problem
in Access. So I manually put in the fields and I am
still getting a blank chart....?- Hide quoted text -

- Show quoted text -

Hi Duane,

Yes, I did see the chart display if I do not have the the following
statement in the query:

HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

What information did you want for the record source?

This is the SQL for the Chart:

TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -

- Show quoted text -

? But that SQL statement is from the chart wizard.

The SQL for the query from which the chart is based is a
select query:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

 

[Opal]

I wasn't kidding when I suggested you needed to set the data types of the
parameters.  Did you try it?

--
Duane Hookom
Microsoft Access MVP

If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate     Date/Time
Forms!frmSearchrpts!txtStartDate   Date/Time
--
Duane Hookom
Microsoft Access MVP
:
On Aug 15, 12:41 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide some information about your report's record source and the
chart row source?
Did you see any chart display when the link properties were blank?
--
Duane Hookom
Microsoft Access MVP
:
On Aug 14, 9:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
The Chart control has Link Master/Child properties. Your's may be set wrong.
--
Duane Hookom
Microsoft Access MVP
:
I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see.  The query is as follows:
SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));
And works fine.  When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.
However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?- Hide quoted text -
- Show quoted text -
Sorry Duane, I am new to charting in Access.  I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:
"Can't build a link between unbound forms."
Can you direct me on how to overcome this error?- Hide quotedtext -
- Show quoted text -
Ah, further research reveals this to be a common problem
in Access.  So I manually put in the fields and I am
still getting a blank chart....?- Hide quoted text -
- Show quoted text -
Hi Duane,
Yes, I did see the chart display if I do not have the the following
statement in the query:
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));
What  information did you want for the record source?
This is the SQL for the Chart:
TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -
- Show quoted text -

? But that SQL statement is from the chart wizard.

The SQL for the query from which the chart is based is a
select query:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));- Hide quoted text -

- Show quoted text -

Hi Duane,

This was new to me....I am in the cross tab query and have added a
PARAMETERS
statement to the beginning of the query (before TRANSFORM) but am
getting syntax
error....the cursor is highlighting the 'slash' between Date/Time

 

[Opal]

I wasn't kidding when I suggested you needed to set the data types of the
parameters.  Did you try it?

--
Duane Hookom
Microsoft Access MVP

If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate     Date/Time
Forms!frmSearchrpts!txtStartDate   Date/Time
--
Duane Hookom
Microsoft Access MVP
:
On Aug 15, 12:41 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide some information about your report's record source and the
chart row source?
Did you see any chart display when the link properties were blank?
--
Duane Hookom
Microsoft Access MVP
:
On Aug 14, 9:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
The Chart control has Link Master/Child properties. Your's may be set wrong.
--
Duane Hookom
Microsoft Access MVP
:
I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see.  The query is as follows:
SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));
And works fine.  When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.
However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?- Hide quoted text -
- Show quoted text -
Sorry Duane, I am new to charting in Access.  I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:
"Can't build a link between unbound forms."
Can you direct me on how to overcome this error?- Hide quotedtext -
- Show quoted text -
Ah, further research reveals this to be a common problem
in Access.  So I manually put in the fields and I am
still getting a blank chart....?- Hide quoted text -
- Show quoted text -
Hi Duane,
Yes, I did see the chart display if I do not have the the following
statement in the query:
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));
What  information did you want for the record source?
This is the SQL for the Chart:
TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -
- Show quoted text -

? But that SQL statement is from the chart wizard.

The SQL for the query from which the chart is based is a
select query:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));- Hide quoted text -

- Show quoted text -

Duane.....I think I got it!! Boy that was painful!! I went through
the
menu to select the parameters....had to play around with the
square brackets, but I got it to work!

One more question, if there is no value in cboShop or cboDept or
cboZone
I would still like the chart to be produced, how can I accomplish
this....?

 

[Duane Hookom]

You would use something like:
(Problem.ShopID=Forms!frmSearchrpts!cboFilter1 Or
Forms!frmSearchrpts!cboFilter1 Is Null)

--
Duane Hookom
Microsoft Access MVP

I wasn't kidding when I suggested you needed to set the data types of the
parameters. Did you try it?

--
Duane Hookom
Microsoft Access MVP

On Aug 15, 4:19 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate Date/Time
Forms!frmSearchrpts!txtStartDate Date/Time

:
On Aug 15, 12:41 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide some information about your report's record source and the
chart row source?

Did you see any chart display when the link properties were blank?

:
On Aug 14, 9:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:

The Chart control has Link Master/Child properties. Your's may be set wrong.

:
I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see. The query is as follows:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

And works fine. When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.

However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?- Hide quoted text -

- Show quoted text -

Sorry Duane, I am new to charting in Access. I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:

"Can't build a link between unbound forms."

Can you direct me on how to overcome this error?- Hide quoted text -

- Show quoted text -

Ah, further research reveals this to be a common problem
in Access. So I manually put in the fields and I am
still getting a blank chart....?- Hide quoted text -

- Show quoted text -

Hi Duane,

Yes, I did see the chart display if I do not have the the following
statement in the query:

HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

What information did you want for the record source?

This is the SQL for the Chart:

TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -

- Show quoted text -

? But that SQL statement is from the chart wizard.

The SQL for the query from which the chart is based is a
select query:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));- Hide quoted text -

- Show quoted text -

Duane.....I think I got it!! Boy that was painful!! I went through
the
menu to select the parameters....had to play around with the
square brackets, but I got it to work!

One more question, if there is no value in cboShop or cboDept or
cboZone
I would still like the chart to be produced, how can I accomplish
this....?

 

[Opal]

You would use something like:
(Problem.ShopID=Forms!frmSearchrpts!cboFilter1 Or
Forms!frmSearchrpts!cboFilter1 Is Null)

--
Duane Hookom
Microsoft Access MVP

I wasn't kidding when I suggested you needed to set the data types ofthe
parameters.  Did you try it?
--
Duane Hookom
Microsoft Access MVP
:
On Aug 15, 4:19 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate     Date/Time
Forms!frmSearchrpts!txtStartDate   Date/Time
--
Duane Hookom
Microsoft Access MVP
:
On Aug 15, 12:41 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide some information about your report's record source and the
chart row source?
Did you see any chart display when the link properties were blank?
--
Duane Hookom
Microsoft Access MVP
:
On Aug 14, 9:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
The Chart control has Link Master/Child properties. Your's may be set wrong.
--
Duane Hookom
Microsoft Access MVP
:
I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see.  The query is as follows:
SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem..DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));
And works fine.  When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.
However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?- Hide quoted text -
- Show quoted text -
Sorry Duane, I am new to charting in Access.  I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:
"Can't build a link between unbound forms."
Can you direct me on how to overcome this error?- Hide quoted text -
- Show quoted text -
Ah, further research reveals this to be a common problem
in Access.  So I manually put in the fields and I am
still getting a blank chart....?- Hide quoted text -
- Show quoted text -
Hi Duane,
Yes, I did see the chart display if I do not have the the following
statement in the query:
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));
What  information did you want for the record source?
This is the SQL for the Chart:
TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -
- Show quoted text -
? But that SQL statement is from the chart wizard.
The SQL for the query from which the chart is based is a
select query:
SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));- Hide quoted text -
- Show quoted text -

Duane.....I think I got it!!  Boy that was painful!!  I went through
the
menu to select the parameters....had to play around with the
square brackets, but I got it to work!

One more question, if there is no value in cboShop or cboDept or
cboZone
I would still like the chart to be produced, how can I accomplish
this....?- Hide quoted text -

- Show quoted text -

Cool! Thanks so much for your help. I have a better understanding
of crosstab queries and charts now! Have fun with your Woot
Monkeys

 

[Duane Hookom]

Glad you are getting this all figured out. I am moving in about 10 days and
my wife wanted to put a couple of the monkeys on the moving sale until I
suggested they would sell nice next to her Longaberger baskets. (We're moving
them)
--
Duane Hookom
Microsoft Access MVP

You would use something like:
(Problem.ShopID=Forms!frmSearchrpts!cboFilter1 Or
Forms!frmSearchrpts!cboFilter1 Is Null)

--
Duane Hookom
Microsoft Access MVP

On Aug 15, 11:44 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I wasn't kidding when I suggested you needed to set the data types of the
parameters. Did you try it?

:
On Aug 15, 4:19 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
If you have a crosstab query, you must specify the data types of all
parameters. For instance
Forms!frmSearchrpts!txtEndDate Date/Time
Forms!frmSearchrpts!txtStartDate Date/Time

:
On Aug 15, 12:41 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Can you provide some information about your report's record source and the
chart row source?

Did you see any chart display when the link properties were blank?

:
On Aug 14, 9:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:

The Chart control has Link Master/Child properties. Your's may be set wrong.

:
I am trying to create a chart in Access 2003 that will allow
the user to enter the parameters from a form for what
details they want to see. The query is as follows:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem..DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

And works fine. When the user selects the command button to view the
chart I ensure that the frmSearchrpts! visible property is false so
that it
is not closed.

However, when I try to open the report, from this form, it is blank. I
cannot
figure out why....can anyone offer any advice?- Hide quoted text -

- Show quoted text -

Sorry Duane, I am new to charting in Access. I used the Chart Wizard
to create my chart, and when I go into the Link Child/Master
properties
I get an error message that states:

"Can't build a link between unbound forms."

Can you direct me on how to overcome this error?- Hide quoted text -

- Show quoted text -

Ah, further research reveals this to be a common problem
in Access. So I manually put in the fields and I am
still getting a blank chart....?- Hide quoted text -

- Show quoted text -

Hi Duane,

Yes, I did see the chart display if I do not have the the following
statement in the query:

HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));

What information did you want for the record source?

This is the SQL for the Chart:

TRANSFORM Sum(qryChart2.CountOfProblemID) AS SumOfCountOfProblemID
SELECT (Format([ProblemDate],"mmm"" '""yy")) AS Expr1
FROM qryChart2
GROUP BY (Year([ProblemDate])*12+Month([ProblemDate])-1),
(Format([ProblemDate],"mmm"" '""yy"))
PIVOT qryChart2.Status;- Hide quoted text -

- Show quoted text -

? But that SQL statement is from the chart wizard.

The SQL for the query from which the chart is based is a
select query:

SELECT Count(Problem.ProblemID) AS CountOfProblemID,
Problem.ProblemDate, Problem.ShopID, Problem.DeptID, Problem.ZoneID,
Status.Status
FROM Status INNER JOIN Problem ON Status.StatusID=Problem.StatusID
GROUP BY Problem.ProblemDate, Problem.ShopID, Problem.DeptID,
Problem.ZoneID, Status.Status
HAVING (((Problem.ProblemDate) Between Forms!frmSearchrpts!
txtStartDate And Forms!frmSearchrpts!txtEndDate) And
((Problem.ShopID)=Forms!frmSearchrpts!cboFilter1) And
((Problem.DeptID)=Forms!frmSearchrpts!cboSearchDept) And
((Problem.ZoneID)=Forms!frmSearchrpts!cboZone));- Hide quoted text -

- Show quoted text -

Duane.....I think I got it!! Boy that was painful!! I went through
the
menu to select the parameters....had to play around with the
square brackets, but I got it to work!

One more question, if there is no value in cboShop or cboDept or
cboZone
I would still like the chart to be produced, how can I accomplish
this....?- Hide quoted text -

- Show quoted text -

Cool! Thanks so much for your help. I have a better understanding
of crosstab queries and charts now! Have fun with your Woot
Monkeys