Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    summing report section footer (2000)

    Hi, I have a report with 2 calculated text boxes (factoryover and factoryunder) in the detail section of a report. The calculations are to pick up whether the difference between 2 fields are "over budget" or "under budget". I use iif(([hours]<[budgethours]),([budgethours]-[hours]),"") for factoryunder (and similar for factoryover) so that the figure is only displayed if it is a positive value. This whole thing is working really well and doesn't need to change.

    The problem I have is that in the section footer I want to be summing the factoryover and factoryunder text boxes. I thought I should be able to just use a text box with control source of =sum([factoryover]) to do this, but when I preview the report I get "Enter parameter value" for factoryover dialog come up. Clicking OK leaves the summing in the footer blank. Can anyone help me resolve this?

    Thank you very much. Regards Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summing report section footer (2000)

    Hello Roger,

    You can't compute the sum of a control in a report; you must sum the expression in the control source of that control. For example:

    =Sum(IIf(([hours]<[budgethours]),([budgethours]-[hours]),0))

    I replaced "" by 0 to avoid type mismatch errors.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: summing report section footer (2000)

    Another way to do this is to introduce a calculated control in the underlying query (I am assuming this) that does the following Calculation:
    FactoryUnder:iif(([hours]<[budgethours]),([budgethours]-[hours]),0)

    Then you can use this as the control source of the textbox in the section footer, eg.
    =sum([FactoryUnder])

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: summing report section footer (2000)

    Thanks for the replies. The reason I originally included the "" as the final argument in the iif calculation was so that if the value was zero, then the text box will be blank. I want to be able to continue to do this. Could you explain what is meant by the type mismatch error and how I can still accomplish my goal of keeping the text box blank if zero? (without using conditional formatting, unless this is actually the best option).

    Thanks. Roger

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summing report section footer (2000)

    The method I proposed would let you keep the original expression (with "") in the Detail section, while using 0 in the expression in the report footer used to sum the values.

    You can use Pat's method and set the Format property of the text box in the detail section to hide 0 (zero) values, by specifying "" in the third section of the Format property (see the online help for Format, and click on Number and Currency data types)

    Setting a value to "" will force it to be text; this could cause problems if you try to calculate the sum.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: summing report section footer (2000)

    I omitted to tell you to only use the additional field from the query for the sum in the section footer.

    Don't use this extra field for the control in the detail section.

Posting Permissions

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