Why does a "Continuous" Form in Access produce "#Error" in a text box?

I am developing an Access 2007 "project" (.adp) as a front-end to data stored in a SQL-Server 2005 Express database.

In the Access application I have "Continuous" Form that lists records from a Table as rows.
In the "Form Footer" section I have 3 text box controls that count and sum the values from the displayed rows.

txtLineCount, data source : "=Count([ShopSales_UniqID])"
txtTotBalnQnty, data source : "=Sum([ShopSales_Qnty])"
txtTotBalnValue, data source : "=Sum([ShopSales_Value])"

These 3 text boxes display the expected values OK, but my problem arises when I try to added a 4th text box in the Form Footer.

txtTotCostValue, data source : "=Sum([ShopSales_Qnty] * [ShopSales_CostPrice])"

The "ShopSales_CostPrice" field is part of the underlying Table and is also displayed in each row. I want to display the total cost value in the Form Footer.

When I add this 4th text box I get "#Error" displayed in all 4 text boxes in the Form Footer.
I can understand there might be a problem with my "data source" syntax for my 4th box (or that "Continuous" Forms don't like such data manipulations) but why would it cause "#Error" to appear in the original 3 text boxes that work OK when the 4th box is removed?

Can anyone explain what is happening? Many thanks.
Who is Participating?
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.

Does this variation work?

Sum([ShopSales_Qnty]) * Sum([ShopSales_CostPrice])
Also, have you ensured that ShopSales_CostPrice is included as a field in the form's recordsource, and is spelled correctly, etc...?
Dale FyeOwner, Developing Solutions LLCCommented:
have you tried:

=Sum(NZ([ShopSales_Qnty],0) * NZ([ShopSales_CostPrice],0))
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

colinasadAuthor Commented:
Thanks for the very prompt replies, mbizup.

Your first suggestion produces the same "#Error" message in all 4 boxes.
I'm not sure that the arithmetic would be correct anway. Would that not sum all the quantities and sum all the unit cost prices then multiply these 2 values. That would not always be the same as the sum of all the individual products.

I'm pretty sure my typing is OK. When I just do a "sum" on one or other of the fields I get the correct value (duplicating what is already in the other 3 boxes), but as soon as try doing a multiplication in the data source I get the error.

I could understand the "#Error" appearing in my 4th text box alone, but why does it also appear in my other 3 (unaltered) text boxes which work OK normally?
colinasadAuthor Commented:
Thanks also for your suggestion, fyed.

Strangely, introducing "Nz" into the mix actually causes more problems.

The multiplication you suggest still causes "#Error" to appear in all 4 text boxes.

But, even using "Nz" in the  simpler data source expression, "=Sum(Nz([ShopSales_Qnty], 0))" also causes the "#Error" result. Without the "Nz", the "ShopSales_Qnty" values would be summed OK.

That seems strange because I would have thought that "Nz" would help make the calculation more robust.

Is it an issue with just that field?

Try this, on its own:

= Sum([ShopSales_CostPrice])
And just double-checking...

ShopSales_CostPrice is a FIELD in the underlying recordsource, right?  (as opposed to a textbox or other control)
Dale FyeOwner, Developing Solutions LLCCommented:
have you checked your references?

Can you take a screen shot of your form and post it here.
colinasadAuthor Commented:
Thanks again for your replies.

Yes, "=Sum(ShopSales_CostPrice)" works OK, but just sums the "unit prices" of each row displayed on the screen.
Similary, "=Sum(ShopSales_Qnty)" also works OK (and is already used in one of the first 3 text boxes).
The problem arises as soon as I try to combine these two fields into the text box's "data source".
They are both fields in the Form's underlying data source, which is a Table in the SQL Server database.

I have attached a Word document containing 2 screen dumps. The text box in question is at the bottom of the "Unit Ready" column that contains the "ShopSales_CostPrice" field.
The first screen dump shows all values displayed OK when the data source is just a simple sum of one field. The second image shows what happens when I try to introduce multiplication into the data source.

I may just have to add a "ShopSales_CostValue" field into the underlying Table, so that I can do a sum on a single new field. But that does seem a bit cumbersome considering it should just be a calculation on two fields that already exist.
>> I may just have to add a "ShopSales_CostValue" field into the underlying Table

Fwiw, That sounds like a fairly simple solution - and one that I would use myself, with the difference that I would use a query rather than adding the field to your table.  So by using something like this as the recordsource for your form:

SELECT ShopSales_UniqID, ShopSales_Qnty,ShopSales_CostPrice,  ShopSales_Value, ShopSales_Qnty * ShopSales_CostPrice AS ShopSales_CostValue
FROM YourTable...

And then sum the calculated query field as you described.

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
Is the recordsource of your form currently JUST the table, or is it a query?

Can you post the recordsource of your form, and a screenshot showing your table's design?
colinasadAuthor Commented:
Thanks, mbizup.
In the Form_Open procedure I set my Form's ".recordsource" to be "SELECT *, ShopSales_Qnty * ShopSales_CostPrice AS COSTVALUE FROM TABLEXYZ" and made my problem text box's "data source" to be "=Sum([COSTVALUE])" and it worked!
I guess "Continuous" Access Forms just don't like controls to have data manipulations in their data source.
Many thanks.
<<I guess "Continuous" Access Forms just don't like controls to have data manipulations in their data source.>>

You've perked my curiosity - and I'm going to test that out when I get a chance.

I have never had any trouble with data maniuplation like that - ie:

SUM ([Field1] *123)

And I THINK that I have used multiple fields like that in the past with no trouble:

SUM (Field1 + Field2)

The one problem I *have* run into when using aggregate functions in control sources is if a field is one of those new "Calculated" field types that Access 2007 brought upon us.  Those do need to be manipulated through a query (but I don't think that was the issue here).
colinasadAuthor Commented:
And the other strange thing was that a problem with one text box control impacted on three others.
If you do discover anything further I think you can post again to this question.
Thanks & regards.
Ok - I'll let you know tonight how accurate my memory is on this one...
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.