2nd post - Crosstab query column headings

[Guest]

Following is the SQL for a crosstab query that I use as the record source for
a chart in a report. How can I get actual months as column headings instead
of M0, M1, M2, etc.? If cmdBegin is 1/1/05, I need the first column in the
query to be Jan. 05, not M0. Any help is appreciated. Thank you.

TRANSFORM Max(Super_D1Tots_Q1.[Day 1 Performance]) AS [MaxOfDay 1
Performance]
SELECT Super_D1Tots_Q1.[RCD LOC]
FROM Super_D1Tots_Q1
GROUP BY Super_D1Tots_Q1.[RCD LOC]
PIVOT "M" & DateDiff("m",[forms]![Pick_Super_Chart]![cmdBegin],[NewDate]) In
("M0","M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11");

 

[Duane Hookom]

You might be able to modify some code from ACG Soft's
http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm
graphing/reports tips page. They have code that shows how to modify
attributes of the graph's datasheet. Perhaps you could stuff actual date
values into the datasheet.

 

[Rob Oldfield]

You could write some code to generate the SQL that would modify the field
names of your query to what you actually require....

sql="select m0 as "&format(cmdbegin,"mmm-yy")&"......"

and then base the chart on that.

Note you can use a loop in the above idea...

sql=""
for i=0 to 11
sql=sql&"select m"&cstr(i)&" as
"&format(dateadd("m",i,cmdbegin),"mmm-yy")&","
next
'chop off trailing comma and add rest of sql statement