We help IT Professionals succeed at work.

Access continuous form, sum in footer

etech0
etech0 used Ask the Experts™
on
I have a continuous form in Access. I created an unbound textbox in the footer, and want it to display the sum of a certain field. How can I accomplish this?
So far, I've tried setting the datasource as =sum([fieldname]), as well as =DSum([fieldname],[querytheformisbasedin]), but both to no avail.
Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill RossProgrammer

Commented:
Hi,

This syntax is correct for the ControlSource od the control.

=sum([fieldname])

This must be in the Report Footer - not the page footer.

Regards,

Bill

Author

Commented:
Hi!
I'm in a form, not a report.
Bill RossProgrammer

Commented:
OOps my mistake.  The Then the syntax is the same in the Form Footer.
Top Expert 2016

Commented:
what is the record source of the form?

this  =sum([fieldname]) should work...

try this by creating a continuous form based on a table


..maybe you are not picking the right column name from your query

Author

Commented:
The record source of the form is a query. I can't make it a table, as it's a very complicated aggregate query with calculations.
Bill RossProgrammer

Commented:
Hi,

Be sure to include the equal sign in the ControlSource property for the text box.

=sum([fieldname])

will work.

Bill
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Try this. On your Form (or Report), give your control a Name (the Name property) of

txtFieldName   where FieldName is your ACTUAL field name,  Then, set the Control Source property to

=[FieldName]

If this doesn't work, then something else is going on.

BTW ... when you say "doesn't work", what exactly are you seeing ?  #Error?  #Name ?

what?

mx
Top Expert 2016

Commented:
<I can't make it a table, as it's a very complicated aggregate query with calculations.>

i said as a test use a table..

now, what is the name of the column with calculations?

Author

Commented:
The column is called PendingES. I'm going to test with a table now.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
etech0:
Did you try renaming the Control as I suggested ... and again ... please define "Not Working"

mx
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Note that if a NULL is in any of the data, your not going to get a total.  Also, if the form is not displaying any records, your not going to get a total.

Jim.

Author

Commented:
I tried renaming the control.

The textbox shows #value.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
What is the data type for this Field ?  And does that field in this complicated query show data by itself - if you just run the query ?  And as noted above, are there any Null values ?

mx

Author

Commented:
Data type is number. There are no Null values, in the form, and when I run the query, they all show numbers.
There will always be records - each record is a person, which won't change.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok ...

So, you renamed the Control in the Footer to

txtPendingES

And your expression for the Control Source is

=Sum([PendingES])  

correct ?

And you are getting #Value ?
I only recall one other Q (at most) where someone said they saw #Value ...  is this an Access db where the data is ?

mx

Author

Commented:
Control Name is PendingEStxt
Control source for sum is sum([PendingES])
I'm getting #name, not #value - sorry for the error.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Can you upload the db?

Author

Commented:
Will put together a sample...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok ... be sure sample exhibits the issue, and explain exactly how to reproduce issue.

thx.mx

Author

Commented:
Open the [only] form to see the issue. At the bottom, the totals should show, but don't.
Sample.mdb
Database Architect / Application Developer
Top Expert 2007
Commented:
For the ES ... you are  missing the equals sign in the expression for the Control Source

=Sum([PendingES])
^

You have

Sum([PendingES])
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
For your other expression, you are missing the Double Quotes around the arguments


=DSum("[PendingDB]","[CatWebWork2CopywritersQ]")
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
1

Author

Commented:
Wow. Which way is better - Sum or DSUM?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Sum for sure.  DSum is overkill.  You would use DSum() only if ... the field you need to Sum was not showing in the RecordSource, or possibly from a completely different query.

Just remember when using aggregate functions on Forms/Reports, you must use the actual field name (you did) ... and *not* the name of the Control.  And on Reports especially, you MUST name the Control different from the Control Source field ... eg:

Name:  txtSomeFieldName
Control Source:  =Sum([SomeFieldName])

Best to do this on Forms also.

Author

Commented:
Thanks!!!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You are welcome.
Bill RossProgrammer

Commented:
Just a note from above that's exactly the solution we mentioned earlier...

by: BillDenverPosted on 2012-04-17 at 09:14:41ID: 37856682