Chart problem

G

Guest

To give a bit of background, I have a form which has a number field for
month (Jan=1, Feb=2) so there are 12 records. In the subform I have weekly
information so when I navigate through the months, the records in the subform
are filtered for each month.

Now I want to repeat this principle with charts so when I scroll through the
months, the SQL source for the chart is filtered and the chart changes. I get
the following error message:

"The Microsoft Jet database does not recognise "[hours count
chart].[period]" as a valid field name or expression"

The only alternative is create a separate chart for each month.

cheers
 
A

Al Campagna

Scuba,
Pretty hard to be exact about this...
So, you have 12 Main form records... one for each MonthNumber, each with their own
related subform? I asssume so...

The chart has a query behind it. That query should be able to use the form field value
as it's criteria.

MonthNumber
= Forms!YourFormName!MonthNumber

As you move from record to record, and Requery the chart each time... the chart should
change to reflect the current MonthNumber on the form.
I think the problem is in your Chart query, and how you refer to the form value to
criteria it.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
G

Guest

Thanks for the reply,

I have one subform that contains a single chart. I want to filter the data
depending on the month. I have tried putting the criteria in the source query
but I still get the same error.

The only thing to do would be to keep it as a list of records, then I should
be able to make it work.


Al Campagna said:
Scuba,
Pretty hard to be exact about this...
So, you have 12 Main form records... one for each MonthNumber, each with their own
related subform? I asssume so...

The chart has a query behind it. That query should be able to use the form field value
as it's criteria.

MonthNumber
= Forms!YourFormName!MonthNumber

As you move from record to record, and Requery the chart each time... the chart should
change to reflect the current MonthNumber on the form.
I think the problem is in your Chart query, and how you refer to the form value to
criteria it.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


scubadiver said:
To give a bit of background, I have a form which has a number field for
month (Jan=1, Feb=2) so there are 12 records. In the subform I have weekly
information so when I navigate through the months, the records in the subform
are filtered for each month.

Now I want to repeat this principle with charts so when I scroll through the
months, the SQL source for the chart is filtered and the chart changes. I get
the following error message:

"The Microsoft Jet database does not recognise "[hours count
chart].[period]" as a valid field name or expression"

The only alternative is create a separate chart for each month.

cheers
Click to expand...
Click to expand...
 
G

Guest

Hello,

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 appreciate I'm probably not making much sense. What can you suggest to
solve what I would like to do?






Al Campagna said:
Scuba,
Pretty hard to be exact about this...
So, you have 12 Main form records... one for each MonthNumber, each with their own
related subform? I asssume so...

The chart has a query behind it. That query should be able to use the form field value
as it's criteria.

MonthNumber
= Forms!YourFormName!MonthNumber

As you move from record to record, and Requery the chart each time... the chart should
change to reflect the current MonthNumber on the form.
I think the problem is in your Chart query, and how you refer to the form value to
criteria it.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


scubadiver said:
To give a bit of background, I have a form which has a number field for
month (Jan=1, Feb=2) so there are 12 records. In the subform I have weekly
information so when I navigate through the months, the records in the subform
are filtered for each month.

Now I want to repeat this principle with charts so when I scroll through the
months, the SQL source for the chart is filtered and the chart changes. I get
the following error message:

"The Microsoft Jet database does not recognise "[hours count
chart].[period]" as a valid field name or expression"

The only alternative is create a separate chart for each month.

cheers
Click to expand...
Click to expand...
 
A

Al Campagna

Well, it sounds like the initial Crosstab query is a pass-through. I would ceratinly
expect that that query could have a criteria that would use a Month value to filter it's
results (how you deliver that value to the pass-through query is your call... value on a
form, parameter, input box... etc...)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

scubadiver said:
Hello,
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 appreciate I'm probably not making much sense. What can you suggest to
solve what I would like to do?

Al Campagna said:
Scuba,
Pretty hard to be exact about this...
So, you have 12 Main form records... one for each MonthNumber, each with their own
related subform? I asssume so...

The chart has a query behind it. That query should be able to use the form field
value
as it's criteria.

MonthNumber
= Forms!YourFormName!MonthNumber

As you move from record to record, and Requery the chart each time... the chart
should
change to reflect the current MonthNumber on the form.
I think the problem is in your Chart query, and how you refer to the form value to
criteria it.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


scubadiver said:
To give a bit of background, I have a form which has a number field for
month (Jan=1, Feb=2) so there are 12 records. In the subform I have weekly
information so when I navigate through the months, the records in the subform
are filtered for each month.

Now I want to repeat this principle with charts so when I scroll through the
months, the SQL source for the chart is filtered and the chart changes. I get
the following error message:

"The Microsoft Jet database does not recognise "[hours count
chart].[period]" as a valid field name or expression"

The only alternative is create a separate chart for each month.

cheers
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I've got it working :)

Al Campagna said:
Well, it sounds like the initial Crosstab query is a pass-through. I would ceratinly
expect that that query could have a criteria that would use a Month value to filter it's
results (how you deliver that value to the pass-through query is your call... value on a
form, parameter, input box... etc...)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

scubadiver said:
Hello,
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 appreciate I'm probably not making much sense. What can you suggest to
solve what I would like to do?

Al Campagna said:
Scuba,
Pretty hard to be exact about this...
So, you have 12 Main form records... one for each MonthNumber, each with their own
related subform? I asssume so...

The chart has a query behind it. That query should be able to use the form field
value
as it's criteria.

MonthNumber
= Forms!YourFormName!MonthNumber

As you move from record to record, and Requery the chart each time... the chart
should
change to reflect the current MonthNumber on the form.
I think the problem is in your Chart query, and how you refer to the form value to
criteria it.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."



To give a bit of background, I have a form which has a number field for
month (Jan=1, Feb=2) so there are 12 records. In the subform I have weekly
information so when I navigate through the months, the records in the subform
are filtered for each month.

Now I want to repeat this principle with charts so when I scroll through the
months, the SQL source for the chart is filtered and the chart changes. I get
the following error message:

"The Microsoft Jet database does not recognise "[hours count
chart].[period]" as a valid field name or expression"

The only alternative is create a separate chart for each month.

cheers
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to chart months by financial year 0
Update chart after filter 1
Chart help 3
Pivot Chart 1
Show empty months in pivot chart 1
update chart after filter 2
How To Add Filter A Pie Pivot Chart When it is loaded In Code 8
Variable series multi line chart? 1

Top