Link to home
Start Free TrialLog in
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?

--Steve
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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.

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.
Avatar of SteveL13

ASKER

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.  

?????????
The three field data sources are coming from a SQ view if that matters.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That was it.  Thanks.
That was it.  Thanks.