I'm trying to use a form Control in a Form which I pass to a report header. This is a variable used in the report.
The Form Control is [Forms]![checkmax]![WeekNumber]
The DataSource for the report is called [ModeMasterTbl] where [Week] is a field in the table that I want to use to summarize [Total OT Hours] by week by dept/manager/employee.
The syntax I'm trying in the control source of the report is not working.
=(Sum((IIf([ModeMasterTbl].[Week]="[Forms]![checkmax]![WeekNumber]",([Total OT Hours]),0),Null)))
Any suggestions on this expression syntax? Would greatly appreciate your suggestions. Thank you.
Below is a snapshot of the data table
Below is the report section where I'm trying to add the control source. You will see I'm trying to use Current Week as a column header in the report and then subtracting 1 for each preceding 11 weeks so I have a trailing 12-week report for reviewing Overtime.
The Form Control is [Forms]![checkmax]![WeekNumber]
The DataSource for the report is called [ModeMasterTbl] where [Week] is a field in the table that I want to use to summarize [Total OT Hours] by week by dept/manager/employee.
The syntax I'm trying in the control source of the report is not working.
=(Sum((IIf([ModeMasterTbl].[Week]="[Forms]![checkmax]![WeekNumber]",([Total OT Hours]),0),Null)))
Any suggestions on this expression syntax? Would greatly appreciate your suggestions. Thank you.
Below is a snapshot of the data table
Below is the report section where I'm trying to add the control source. You will see I'm trying to use Current Week as a column header in the report and then subtracting 1 for each preceding 11 weeks so I have a trailing 12-week report for reviewing Overtime.