Chart problem: At a loss :-(

[Guest]

I have posted this problem in the "form" thread but have decided to post here
(apologies in advance for breaking internet forum guidelines!).

To give you an example first:

I have a form which has a single field as its data source and this data is
in a single text box.

Within the form I have a subform which contains lists of records. I have
managed to set it up so that, when I go from one record to the next in the
main form, the list in the subform is filtered.

I want to do the same with a chart (so when I go from one record to the
next, the SQL query changes).

I have the following queries:

"Avehours src"

Which create a crosstab

"AveHours src_Crosstab"

Which I use to create another select query

"AveHrs"

and I use this last query as the source for the chart.

I would like to link this last query with the text box in the form.

At the moment the only way I can filter the data in the chart is if I
specify an explicit critera (like 1,2,3,4 etc) rather than a form reference.

I am at a loss.

thanks

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So in your case the parameter should read something like
[Forms]![YourFormName]![YourControlName]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks,

I didn't know about that. Although the query saves ok, when I try opening
the form, I get an "invalid bracketing" error.

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So in your case the parameter should read something like
[Forms]![YourFormName]![YourControlName]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have posted this problem in the "form" thread but have decided to post
here
(apologies in advance for breaking internet forum guidelines!).

To give you an example first:

I have a form which has a single field as its data source and this data is
in a single text box.

Within the form I have a subform which contains lists of records. I have
managed to set it up so that, when I go from one record to the next in the
main form, the list in the subform is filtered.

I want to do the same with a chart (so when I go from one record to the
next, the SQL query changes).

I have the following queries:

"Avehours src"

Which create a crosstab

"AveHours src_Crosstab"

Which I use to create another select query

"AveHrs"

and I use this last query as the source for the chart.

I would like to link this last query with the text box in the form.

At the moment the only way I can filter the data in the chart is if I
specify an explicit critera (like 1,2,3,4 etc) rather than a form
reference.

I am at a loss.

thanks

 

[Guest]

I've got it working!!!!

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So in your case the parameter should read something like
[Forms]![YourFormName]![YourControlName]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have posted this problem in the "form" thread but have decided to post
here
(apologies in advance for breaking internet forum guidelines!).

To give you an example first:

I have a form which has a single field as its data source and this data is
in a single text box.

Within the form I have a subform which contains lists of records. I have
managed to set it up so that, when I go from one record to the next in the
main form, the list in the subform is filtered.

I want to do the same with a chart (so when I go from one record to the
next, the SQL query changes).

I have the following queries:

"Avehours src"

Which create a crosstab

"AveHours src_Crosstab"

Which I use to create another select query

"AveHrs"

and I use this last query as the source for the chart.

I would like to link this last query with the text box in the form.

At the moment the only way I can filter the data in the chart is if I
specify an explicit critera (like 1,2,3,4 etc) rather than a form
reference.

I am at a loss.

thanks