Changing Description in CrossTab based on Form Checkbox

[sxschech]

I have this code in a Select Query which works.

SELECT tblChart.RunDate, tblChart.WeekNo, tblChart.Term, tblChart.Status, [red]IIf([Forms]![frmMain]![chkCensus]=True,Replace([tblChart].[DisplayStatus],"Intend","Enrolled"),[tblChart].[DisplayStatus]) AS DisplayStatus[/red], tblChart.TOT
FROM tblChart;

If I add the statement in [red]red[/red] to a crosstab, I get an error "The Microsoft Jet database engine does not recognize '[Forms]![frmMain]![chkCensus]' as a valid field name or expression"

I also tried the statement in a select query that uses the crosstab as its source as well as using the above query as the source for the cross tab. In either case, the error happened whenever the crosstab was involved.

Ultimately I need to be able to change the wording that is shown on a legend in a report and am not sure how else to get the data into the legend other than changing the description displayed by the red statement.

 

[dhookom]

You would need to add [Forms]![frmMain]![chkCensus] with its data type to the query parameters.

Duane
Hook'D on Access
MS Access MVP