Change query column name based on text box value.

[zmbd]

Clarify the quesion:

OK:
I've read and re-read your query... I've taken a look at your example db and I'm not even sure what you're after here (however, if Rabbit doesn't follow, it's got to be something exceptional - so I'm not too embarrassed); however, this is what I think you are asking for:

So work with me here.... I kind-of have to have this written down:
Do I understand the following correctly:
Take chart for "Last 3 Months..." in your example db:
In your example, the legend for this chart shows: "Month 1 = April, 2012"; "Month 2 = May, 2012"; ..."
The chart x-axis currently shows: "Month 1", "Month 2", ...
The x-axis labels are pulled from the query [3monthsgraph] which is based on the the crosstab query [3 months] based.

Is what you would like to have happen is that the x-axis labels take on the values "April, 2012"; "May, 2012"... instead of "Month 1"....

or

have I missed the mark?

-z

[Jerry Maiapu]

zmbd .
You are on the right track and you are right.

How do I achieve what you have just mentioned especially dynamic dates with stable months i.e. even if a date does not fall in a month I want the month to still show and the names "month 1", "month2" etc..converted to its corresponding month names like Jan 2010, Feb 2010 etc.. within the graph.

Assist me if you can sir.

Cheers!

Nazero

[zmbd]

I ran accross just such a thing awhile back... let me dig thru the histroy logs... problem is, I don't remember which PC I was logged in on at the time!

Remember... crosstabs are not a strong point for me:

In the meantime, while I'm hunting for that log entry, maybe something like this could be modified to work???????

http://blogs.office.com/b/microsoft-access/archive/2012/05/23/using-crosstab-queries-in-reports.aspx

The only other thing I can think of is using VBA to rebuild the query on demand.

Rabbit?

[zmbd]

Here's a link to a site with an example that works for the report... I'm currently trying to understand how it works and the code behind the reports.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=362

If you go back the home page for this site there's a ton of information available on the site for other questions.

[Rabbit]

That database must be in 2010 cause I can't open it.

[zmbd]

Jerry's or the one from rogers?
-z

[Rabbit]

Sorry, I should have clarified. I meant Jerry's.

[zmbd]

Tried to convert it to 2003 from 2010... some conversion errors; however, when I reopen the file in 2010 it seems ok still...

[Rabbit]

I assume this is what you're looking for. Which is, in essence, my original suggestion, but tweaked for your data.

[Jerry Maiapu]

Thanks Rabbbit.

I have to add the 6 month and 9 month trend graph on the same form but need to understand first your query content before proceeding.

Anyway thanks for your help.

..that worked.
Will post back with 3, 6 and 9 months trend graphs if i manage to achieve all for the good of those who may need.

[dsatino]

Here's how I would do it. Basically, you set the graph's rowsource as a value list rather than a query and then just build the list programatically . It takes only a small amount of code and a bit extra planning.