Change Field Caption

[Scooter]

What code is needed to dynamically change the field caption of a query
based on a text box on a form? The SQL for my query is:

SELECT [DepCatCompleteWeek].[Week],
[DepCatCompleteYr3Count].[CountOfYear3],
[DepCatCompleteYr2Count].[CountOfYear2],
[DepCatCompleteYr1Count].[CountOfYear1]
FROM ((DepCatCompleteWeek LEFT JOIN DepCatCompleteYr3Count ON
[DepCatCompleteWeek].[Week]=[DepCatCompleteYr3Count].[Week]) LEFT JOIN
DepCatCompleteYr2Count ON
[DepCatCompleteWeek].[Week]=[DepCatCompleteYr2Count].[Week]) LEFT JOIN
DepCatCompleteYr1Count ON
[DepCatCompleteWeek].[Week]=[DepCatCompleteYr1Count].[Week]
ORDER BY [DepCatCompleteWeek].[Week];

The query column caption is based on a value that is from the form
textbox [Forms]![Print Graphs]![txtYear]. So, if the user inputs 2006,
then the [CountofYear#] field captions should read 2004, 2005, and
2006.

Thanks.

 

[Guest]

You could do it by changing the SQL property of the querydef object in code
before opening the query, but I'd suggest a different approach.

Instead of opening the query open a report or form based on the query. You
can then use controls in the form header or the page header of the report
which reference the control on the form from which the bound form or report
is opened. Regardless of this requirement that's how I'd do it anyway. I'd
never open a query in raw datasheet view in an application. A form or report
gives you a lot more control over the appearance and the column headings in
the query are irrelevant.

Ken Sheridan
Stafford, England

 

[Scooter]

My appologies. I should have given more info as to the output data I
need. I'm using the data in the query to create a chart in a report.
The legend on the chart displays the wording based on the field
caption. So, maybe another way to approach this is if there is a way
to dynamically change the legend wording without changing the field
name or caption?

 

[Guest]

It might be possible to change the chart's legend at runtime, but I've had
very little experience with using charts and can't say how, It may well be
simpler just to amend the querydef object's SQL property prior to opening the
report. You can then change the column headings to whatever you wish. You
should not give the columns any Caption property values in this case as the
heading would be determined by the SQL statement rather than the Caption
property. Alternatively you could change the caption property of each Field
object of the querydef object in code, but the former would be simpler as all
that's necessary is to build the SQL statement and then assign it to the SQL
property of the querydef object, referencing the form's txtYear control to
concatenate the correct years into the string expression:

"CountOfYear1 AS " & [Forms]![Print Graphs]![txtYear]
"CountOfYear2 AS " & [Forms]![Print Graphs]![txtYear] + 1
"CountOfYear3 AS " & [Forms]![Print Graphs]![txtYear] + 2

Ken Sheridan
Stafford, England