Avatar of SteveL13
SteveL13Flag for United States of America asked on

Calculated field problem

I have a form with a calculated field on it.  The field name is "txtCombinedEst".  This is supposed to be a calculated sum value of three fields, "txtStockEst", "txtTotalMaterialEst", and "txtOutsidePurchaseEst".  I have tried putting "=[txtStockEst]+[txtTotalMaterialEst]+[txtOutsidePurchaseEst]" in the control source of the calculated field and have also tried putting "Me.txtCombinedEst = [txtStockEst] + [txtTotalMaterialEst] + [txtOutsidePurchaseEst]" in the onopen event of the form.  But all I get is a bunch of ################'s in the field result.

What am I doing wrong?

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
Dale Fye

if any of those fields are empty, then you will get that.

Try, in the control source:

=NZ([StockEst], 0) + NZ([TotalMaterialEst], 0) + NZ([OutsidePurchaseEst], 0)

Make sure you use the actual field name, not the control name.
Dale Fye

BTW, the Open event of the form would be the wrong place to put the code you mentioned above.  In the open event, the recordsource is not visible.  Furthermore, that would only fire once.  You could put it in the Forms Current event, but that would only fire when the record first gets the focus and would not update as you enter data in the fields.  The best place would be to either put it in the control source of an unbound form, or as a computed column in the underlying query.

This is interesting, I put your code in the control source and still get a bunch of #######s.  So out of curiousity I clicked on the calculated field and see...

166.442652.911543.18  which is all three fields put together rather than summed.  

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

The three field data sources are coming from a SQ view if that matters.
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

That was it.  Thanks.

That was it.  Thanks.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.