Chart problem

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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."
 
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...
 
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...
 
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...
 
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
How to create multiple bar charts at the same time? 0
PowerPoint Embed scroll excel chart and data in powerpoint 0
How To Add Filter A Pie Pivot Chart When it is loaded In Code 8
Using =Offset in Charts 1
update chart after filter 2
Chart help 3
Excel 2007 graphs/charts automatically populate with new data 1

Back
Top