Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Sub-Report Total added to Report Sub Total Error

    I have a report that I am trying to add the Sub-Report Total to, but some of the sub-reports are blank since there is no OT for the specific location. The report is by locations and with the Subreport "OT by Employee per Week" at the end. How do I not include the sub-report in the subtotal if sub report is empty?

    The expression I am using is: =Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT]

    Sum([Total]) is the Main Report Total
    [Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT] is the sub-report total

    I have tried an IIf statement which still gave me a "#Error" message

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can use the HasData function to test whether the subreport has any data.

    =IIf(subreport.Report.HasData, use your current expression, use an expression that does not include the subreport )

    =IIf(subreport.Report.HasData, Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT], Sum([Total]))
    Regards
    John



  3. #3
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    John,

    Thank you again for you help, you are always a life saver when it comes to Access. I only have one other question to finalize this report.

    When I try to add the two expressions below, I am back to getting a #Error message in my Total Box. I am sure this is probably a quick fix for you, but I am killing myself trying to figure this out.

    Sub Total (Name of field is: [Sum Of total])
    =IIf([Total Hrs Per Week - OTCalculations (Hrs Worked)1].[Report].[HasData],Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT],Sum([Total]))

    Tax @ 6.5% (Name of Field is: [Tax])
    =IIf([Total Hrs Per Week - OTCalculations (Hrs Worked)1].[Report].[HasData],Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT],Sum([Total]))*.065

    Total (Name of Field is: [Total2])
    =IIf([Total Hrs Per Week - OTCalculations (Hrs Worked)1].[Report].[HasData],Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT],Sum([Total]))+IIf([Total Hrs Per Week - OTCalculations (Hrs Worked)1].[Report].[HasData],Sum([Total])+[Report]![Total Hrs Per Week - OTCalculations (Hrs Worked)1]![Sum of TotalOT],Sum([Total]))*.065

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    So are you saying that the first two expressions work, but that the third, which is just the sum of the first two, does not?

    I would just sum them directly
    =[sum of total] + [tax]
    Last edited by johnhutchison; 2011-09-29 at 18:25.
    Regards
    John



  5. #5
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That gives the #Error message back on all 3 text boxes because you can't add two text boxes in a report

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by brandy9271 View Post
    That gives the #Error message back on all 3 text boxes because you can't add two text boxes in a report
    Yes you can. I do it all the time.
    But you need to be sure that the name of the text boxes are not the same as the names of any of the fields.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •