Trying to Use Form Control as Variable in Report Control Source 2

[JimStrand]

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.

 

[dhookom]

I wouldn't place the form control reference in quotes since it will treat this as a literal string. Try this (although I think you have lots of unnecessary ()s:

=(Sum((IIf([ModeMasterTbl].[Week]=[Forms]![checkmax]![WeekNumber],([Total OT Hours]),0),Null)))

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

Thanks Dwight, I appreciate your help. Removed quotes with this syntax - resulting error message.

=(Sum((IIf([ModeMasterTbl].[Week]=[Forms]![checkmax]![WeekNumber],([Total OT Hours]),0),Null))

Any other suggestions?

 

[dhookom]

I would next attempt to place the control value in the reports record source

OTTotal: [Forms]![checkmax]![WeekNumber],([Total OT Hours]

Then replace the form reference with OTTotal



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

I pasted this prompt into BING Co Pilot and received this:

=Sum(IIf([ModeMasterTbl].[Week] = [Forms]![checkmax]![WeekNumber], [Total OT Hours], 0))

Upon further testing, this result looks good.

 

[Andrzejek]

You also may be able to eliminate (most of) the [ and ] and have just:

[tt]=Sum(IIf(ModeMasterTbl.Week = Forms!checkmax!WeekNumber, [Total OT Hours], 0))[/tt]

since in most places you do not use Space(s), special characters or reserved words in your names.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson