Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

JimStrand

Technical User
May 7, 2014
33
US
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
Screenshot_2024-02-22_184504_u8tmaf.gif


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.

Screenshot_2024-02-22_185828_fyznv9.gif
 
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:

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

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
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?

Screenshot_2024-02-23_092824_ckhudu.gif
 
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
 
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.
Screenshot_2024-02-23_094344_rsgxey.gif
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top