×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

Trying to Use Form Control as Variable in Report Control Source

Trying to Use Form Control as Variable in Report Control Source

(OP)

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.






RE: Trying to Use Form Control as Variable in Report Control Source

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 --> ControlSource

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

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

RE: Trying to Use Form Control as Variable in Report Control Source

(OP)
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?

RE: Trying to Use Form Control as Variable in Report Control Source

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

RE: Trying to Use Form Control as Variable in Report Control Source

(OP)
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.

RE: Trying to Use Form Control as Variable in Report Control Source

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close