I have a text Box on a report with following calculation. [26] represents Week 26 on a Union Qry of crosstab queries which is the data source for the report.
=Sum((IIf([ModeName]="Bus Transportation",IIf([CYBudPY]="CY",(([26])),0),Null)))
This works fine.
In the Header of the Report I have another text box with a reference to the largest week in the datasource. This is week 26
=[Forms]![checkmax]![WeekNumber]
=26
On the report header the 26 appears.
What I want to do is pass this form reference to the report calculation:
Something in the line of:
=Sum((IIf([ModeName]="Bus Transportation",IIf([CYBudPY]="CY",(([Forms]![checkmax]![WeekNumber])),0),Null)))
this syntax doesn't work but I'm hoping someone can help with proper syntax or method to reference the Form Control as this variiable changes every week.
Thank you in advance.
Jim
=Sum((IIf([ModeName]="Bus Transportation",IIf([CYBudPY]="CY",(([26])),0),Null)))
This works fine.
In the Header of the Report I have another text box with a reference to the largest week in the datasource. This is week 26
=[Forms]![checkmax]![WeekNumber]
=26
On the report header the 26 appears.
What I want to do is pass this form reference to the report calculation:
Something in the line of:
=Sum((IIf([ModeName]="Bus Transportation",IIf([CYBudPY]="CY",(([Forms]![checkmax]![WeekNumber])),0),Null)))
this syntax doesn't work but I'm hoping someone can help with proper syntax or method to reference the Form Control as this variiable changes every week.
Thank you in advance.
Jim