Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Calculation Problem (2000)

    I've generally found the best way to handle summing fields is to do it in the query. So, create a field in query with an expression that does the match you want, then name it TotalCommission. Put a textbox in your report footer (and/or group footer) with control source of:
    =sum(TotalCommission)

    As for landscape problem. while in Design mode, trying setting paper size, then save design.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    kennyritch
    Guest

    Re: Report Calculation Problem (2000)

    Thanks for the reply.

    However, I don't know if you noticed or not but the comRate field uses an IIF statement and it's taken me so long to figure out that I'm a little reluctant to change the basis of my report. The query that the Report is based on simply checks for all records by a particular Sales Person where dateBank is Not Null. Very simple.

    Is there really absolutely no way I can simply add up the fields in the right hand column (i.e. those under Commission Due) and show the total at the bottom of the report without doing work on the underlying query?

    Cheers,
    Kenny

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Calculation Problem (2000)

    You cannot calculate using a calculated field. If you want to total that field, then you have to do the calculation in the underlying query. There is no alternative except using a DSum and repeating the underlying calculation in that. It would be easier to just change the query.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Calculation Problem (2000)

    At the risk of getting <img src=/S/warts.gif border=0 alt=warts width=59 height=24> there is a way. If you duplicate the calculated control, give it another name like txtTotalCommission set it to Visible=NO and RunningSum-OverAll, then refer to it in the Footer, i.e. create yet another text box in the footer like - txtCommTotal with control source =[txtTotalCommission], that should do it.

  5. #5
    kennyritch
    Guest

    Re: Report Calculation Problem (2000)

    Thanks Brian - I love those simple cheats <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Cheers,
    Kenny.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Calculation Problem (2000)

    >>However, I don't know if you noticed or not but the comRate field uses an IIF statement <<

    No, I didn't notice. I must confess that some of us see so many messages we sometimes skim them, and may miss certain factors.

    I'd still recommend trying to get it in the query. However, you can manually add the total from any control in the detail section to an unbound control you have in a footer. Just put the appropriate code in the detail section's OnPrint event. HOWEVER!!!!! This is not recommended. For one thing, it produces unpredictable results if you preview the report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    kennyritch
    Guest

    Report Calculation Problem (2000)

    Hi Folks

    Thanks to the help I received on this Board last week I've managed to create my Commission Query and it works!

    However, I'm having a bit of trouble producing a 'Total Commission' calculated field at the bottom of the Report. When I try to Preview the Report, Access asks me to enter values for some of the fields.

    If you look at the following picture you'll see the problem I'm having. I haven't shown fields dateBank and comInitial for space reasons but these fields have different values for each record in my DB. How do I produce a 'summed' field that sums all of the values under 'Commission Due'? I'm stumped.

    Oh, and while you're here ... how do I change my Report so that everytime I open it, it defaults to a Landscape view. At the moment I have to keep changing the setting under 'Page Layout'. Is there something I'm overlooking?

    Cheers for any help guys,
    Kenny.

    **Edited to trim picture size - Rory 19/07/2001**

Posting Permissions

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