1. 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. 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.

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

5. 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. 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.

7. 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
•