Trying to sum numberic values in the footer of a continuous form

I have a continuous form, which is actually a sub-form on a main form, with a numeric field in the detail.  Then in the footer I'm trying to sum those values.  

The field name in the detail is a calculated number named txtAmount

Then in the footer I have:

=Sum([txtAmount])

Isn't working.  Why?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try using the same formula in the Sum method as you're using for the Calculated Field.
SteveL13Author Commented:
Isn't working.  Here is the formula I have in the calculated field:

=[txtQuantity]*[txtRate]

When I copy/paste that into the control for the field in the footer I get just the value of the calculated field in the first record on the continuous form.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you doing this in the Footer of the Subform?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LEARN MORE
SteveL13Author Commented:
Yes.
Gustav BrockCIOCommented:
Try with either:

    =Sum([txtQuantity]*[txtRate])

or:

    =Sum([Quantity]*[Rate])

if Quantity and Rate are the names of the fields of the recordsource.

/gustav
SteveL13Author Commented:
Neither worked.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is the Calculated Field in the Query? You might try doing your calculation there, and then SUM the field from the Query.
Gustav BrockCIOCommented:
Then, I guess, the footer is on the main form. If so, you will have to refer to the controls on the subform.

/gustav
Jeffrey CoachmanMIS LiasonCommented:
You never clearly states what "Footer" you were referring to...
In any event, ...You cannot sum a "Control" (txtAmount)
...you must sum the "Field" (Amount)

So as Scott suggests, ...make the calculation a field in the query, then you can sum it pretty much wherever you like.
sums galoresimple sample attached

JeffCoachman
Database80.mdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
All helped.  Thanks.
Gustav BrockCIOCommented:
You are welcome!

/gustav
Jeffrey CoachmanMIS LiasonCommented:
;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.