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!

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

Recent content by JimStrand

  1. JimStrand

    Trying to Use Form Control as Variable in Report Control Source

    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.
  2. JimStrand

    Trying to Use Form Control as Variable in Report Control Source

    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?
  3. JimStrand

    Trying to Use Form Control as Variable in Report Control Source

    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...
  4. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    I figured out the error. The name of the report in the database needs to match the name which had been exported and saved on the server. Thank you.
  5. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    Thanks Andy, The error is generated by the DoCmd statement: DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All departments.", _ False...
  6. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    OK This tactic worked, and report is done. Thank you Duane! One problem I am having is with email function: DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept.pdf", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All...
  7. JimStrand

    Expression Limit on Sum IIf In Method ? Used in Report Calculation

    I'm using an expression in Report field which aggregates 13 departments. I added one dept at a time to make sure this method was working. When I added the 13th dept my calculation returns #error. =(Sum((IIf([ModeName] In ("Admin excl IT","IT","Police","Mobility","Core Support excl Fac...
  8. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    OK Now I've converted all queries to select queries and now I am having difficulty calculating a moving YTD Average for the Amount. Week 2 for example is taking just the week 2 amount and dividing by 2 when I need the Amounts of week 1 and2 to be included in the YTD Average. Any suggestions on...
  9. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    Hi Duane, I'm redesigning the solution as you've suggested. Convert all crosstab queries into select queries, union them, and then change the report reference. I will let you know if this resolves my issue. Thank you.
  10. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    HI Duane - thank you for your response. UMD Bulldog Alum appreciates your Northern support!. Unfortunately I cannot un-crosstab the query as I have 4 crosstab queries in a union query which is the data source for the report. The 26 represents the current final column in data set and crosstab...
  11. JimStrand

    Using Report Header (Form control) as Variable in Report Expression

    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...
  12. JimStrand

    Crosstab query - returning 1 record per week instead of 1 record

    I figured it out. I had invoice description in one of the row headings and of course this is unique for each week. Thank you!
  13. JimStrand

    Crosstab query - returning 1 record per week instead of 1 record

    I have a crosstab query which is returning a new record for each column header instead of 1 row per Mode.Name TRANSFORM Sum(WK_25_OT_Qry.SumOfEXPN_AMT) AS SumOfSumOfEXPN_AMT SELECT Mode.ModeName FROM CalendarPayroll_TY INNER JOIN ((WK_25_OT_Qry INNER JOIN SOF_Dept_Roll_Up_Tbl ON...
  14. JimStrand

    DoCmd.SendObject (dynamic strObjectName naming syntax)

    Trying to implement this idea Duane. Receiving External Name not defined error. Not sure how to define this renamed object. My report name is defined as "RegionalScorecard-" Trying to rename temporarily to "RegionalScorecard- & " & strEVPFName & "" But receiving error message: Any thoughts...
  15. JimStrand

    DoCmd.SendObject (dynamic strObjectName naming syntax)

    I am trying to combine a variable defined with my strObjectName to customize the report name which will be sent to many differnt recipients: DoCmd.SendObject acSendReport, strObjectName, strOutputFormat, strEVPFEmail, "", "", "May Scorecard", _ "Attached is your May 2017 Scorecard. Please call...
See more

Part and Inventory Search

Back
Top