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.
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.
Also, have you ensured that ShopSales_CostPrice is included as a field in the form's recordsource, and is spelled correctly, etc...?
have you tried:
=Sum(NZ([ShopSales_Qnty],0
=Sum(NZ([ShopSales_Qnty],0
ASKER
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?
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?
ASKER
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],
That seems strange because I would have thought that "Nz" would help make the calculation more robust.
Regards.
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],
That seems strange because I would have thought that "Nz" would help make the calculation more robust.
Regards.
Is it an issue with just that field?
Try this, on its own:
= Sum([ShopSales_CostPrice])
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)
ShopSales_CostPrice is a FIELD in the underlying recordsource, right? (as opposed to a textbox or other control)
have you checked your references?
Can you take a screen shot of your form and post it here.
Can you take a screen shot of your form and post it here.
ASKER
Thanks again for your replies.
Yes, "=Sum(ShopSales_CostPrice)
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.
AccessForm.doc
Yes, "=Sum(ShopSales_CostPrice)
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.
AccessForm.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Can you post the recordsource of your form, and a screenshot showing your table's design?
ASKER
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.
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).
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).
ASKER
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.
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...
Sum([ShopSales_Qnty]) * Sum([ShopSales_CostPrice])