Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum of Row totals

Status
Not open for further replies.
xicana

xicana

Technical User
Dec 11, 2003
100
US
I have a text box in a group that displays the sum of the values in 3 other text boxes (within the same group):

Field Name: Control Source:
textBox1 dlookup(...)
textBox2 dlookup(...)
textBox3 dlookup(...)
Total =nz(Reports![Report]!textBox1,0)+nz (Reports![Report]!textBox2,0)+nz(Reports![Report]!textBox3,0)

Each row in the group looks like this:

textBox1 textBox2 textBox3 Total

I have a TOTALS row where I total up the columns for each row. I also want to get the sum of the Total column...I have tried:
Code: 
=sum(Reports![NameofReport]![Total])
That didn't give me an error - but didn't total anything.
Code: 
=sum([Total])
This one asked me for a [Total] parameter when I ran the report.

Looks like it should be an easy solution...

Sandy
 
Sort by date Sort by votes
Sandy
In the Report Footer, have you tried putting a text box with a structure such as...
=Sum([textBox1]) + Sum([textBox2]) + Sum([textBox3])

You might have to include the Nz for each of those, if there is a possibility of a 0 value.

You could also try Sum(Abs([textBox1]) + etc...

Tom

 
Upvote 0 Downvote
Code: 
=NZ(Sum(Reports![REPORT]!textBox1))+NZ(Sum(Reports![REPORT]!textBox2))+NZ(Sum(Reports![REPORT]!textBox3))

I tried the above - and it gave me zero's all the way down (even when I knew there should be numbers). I tried it without the nz and it didn't give me anything. Also, I have to reference the report I am in - else it asks me for parameters when I run the report.

I also tried using abs()(like you suggested) but that didn't work either. I am putting the totals were they are supposed to be - in the group footer. The fields that I'm trying to get totals for are fields that I put formulas into (calculated fields - see original post)... is that the problem? If so, how do i get around it?

Sandy
 
Upvote 0 Downvote
You can't sum CONTROLS from one section to another. You can sum the expressions from most controls. For instance, if you have a text box:
Name: txtNetCost
Control Source: = Qty * UnitPrice
You can't sum in a footer with:
Control Source: =Sum(txtNetCost)
You can sum in a footer with:
Control Source: =Sum(Qty * UnitPrice)

This looks very slow using dlookup(). I would have to believe there is a better solution.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Thanks for the info Duane...

You're right - using dlookup, Access shuts down on me. I need to get those totals though...I guess I'll have to figure out a way around it...

Thanks guys!
Sandy

Sandy
 
Upvote 0 Downvote
Sandy,

We might be able to help you rid your life of dlookup() if we knew more about your situation.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Thanks to you both...I appreciate your help...we've decided to create this report in crystal...turned out to be way easier.

=)



Sandy
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JimStrand
  • Locked
  • Question
Expression Limit on Sum IIf In Method ? Used in Report Calculation
Replies
1
Views
235
dhookom
dhookom
breezett93
  • Locked
  • Question
Report often takes multiple minutes to load, lots of formatting time between pages
Replies
5
Views
248
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Print multiple copies of report data 1
Replies
5
Views
238
dhookom
dhookom
renigar
  • Locked
  • Question
Access 365 Report use nickname if exist instead of 1st name
Replies
4
Views
415
renigar
renigar
ptrifile
  • Locked
  • Question
Sum with multiple criteria
Replies
2
Views
167
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top