Results 1 to 7 of 7
  1. #1
    kennyritch
    Guest

    Simple Report Calculation Stuff

    Hi All

    Sorry for these simple questions but I'm a bit new to the Report side of Access ...

    1. I'm trying to add together 2 numeric field values on my report so I created a Text Box and in the Control Source I entered the expression =[salesAmount1]+[salesAmount2]. However it only shows a total value when both fields are Not Null. Is there anyway I can have a total shown even if salesAmount2 is Null?

    2. Is it possible to use the calculated value from 1. to perform further calculations in another field or do I have to total the values again in my next field?

    Cheers for any help,
    Kenny.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simple Report Calculation Stuff

    Hi Kenny,
    You can change the expression to =NZ([SalesAmount1])+NZ([SalesAmount2) which will convert the nulls to zeros. For your second question, if your first textbox (the one adding SalesAmount1 and SalesAmount2) is called Text1 for example, you can use an expression like =[Text1]+[SalesAmount3] to add a new amount to the previous sum.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    kennyritch
    Guest

    Re: Simple Report Calculation Stuff

    Thanks for the help ... and here's another!

    My report shows a sale amount for each company in my DB. It also calculates commission for each sale and I hold each value in a Text Box called 'commission'. How do I sum all these commission values and have the total value displayed at the end of the report? I tried =Sum([commission]) but I get #error. Hmmm. Any ideas?

    Cheers,
    Kenny.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simple Report Calculation Stuff

    Kenny,
    It depends on how your commission is calculated. If it's a flat rate (e.g. always Sales * 10%) then you could have a text box in the report footer that uses =sum([Sales])*0.1
    Alternatively, you can calculate the commission in the query underlying the report using a calculated field such as Commission: [Sales]*[comm_rate] and then put a textbox with =sum([commission]) in the report footer.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    kennyritch
    Guest

    Re: Simple Report Calculation Stuff

    Thanks again - BUT, I put =Sum([commission]) at the end of my report and 'Access' keeps asking for me to enter the parameter value for 'commission' whenever I try to view the form. Any idea what's happening?

    Cheers,
    Kenny.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Simple Report Calculation Stuff

    Hmm, curious. Is the field in the underlying query definitely called 'commission'? Easiest way to check is to pull up the field list in the report's design view and make sure it appears exactly the same there as in your expression. Let me know if that doesn't help.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    JGravesNBS
    Guest

    Re: Simple Report Calculation Stuff

    Hi kennyritch & rory

    Having same problem, my scenario follows, would be interested in any solutions.
    ==================================
    NEED REPORT TOTALS - NBS

    I have a report that prints a detail listing that includes the following two columns:

    Name: PermitFees
    Control Sourse: =[BuildFee]+[FilingFee]

    Note: BuildFee & FilingFee are two non-visible controls in the detail line.

    Name: SewerFee

    Control Source: =DSum ("[curFees]","tblBPFees","[tblBPFees].[lngBPermitID] =
    [qryPermitRoadReport]![lngBPermitID] and [tblBPFees].[lngFeeTypeID] =GetPref('Sewer Fee ID')")

    The detail lines for PermitFees & SewerFee print OK

    In the report footer I have two text boxes and want to print a total for the above two columns.

    I've tried many attempts with the expression builder and can't get totals to print.

    What am I doing wrong?

    What should the control source be for Total PermitFees & SewerFee

    Thanks, John Graves

Posting Permissions

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