Solved

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

Posted on 2012-03-30
15
668 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:colinasad
  • 8
  • 5
  • 2
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37786648
Does this variation work?

Sum([ShopSales_Qnty]) * Sum([ShopSales_CostPrice])
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37786662
Also, have you ensured that ShopSales_CostPrice is included as a field in the form's recordsource, and is spelled correctly, etc...?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37786724
have you tried:

=Sum(NZ([ShopSales_Qnty],0) * NZ([ShopSales_CostPrice],0))
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:colinasad
ID: 37786728
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?
0
 

Author Comment

by:colinasad
ID: 37786791
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.

Regards.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37786796
Is it an issue with just that field?

Try this, on its own:

= Sum([ShopSales_CostPrice])
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37786803
And just double-checking...

ShopSales_CostPrice is a FIELD in the underlying recordsource, right?  (as opposed to a textbox or other control)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37786806
have you checked your references?

Can you take a screen shot of your form and post it here.
0
 

Author Comment

by:colinasad
ID: 37786903
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.
AccessForm.doc
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37786929
>> 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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37786962
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?
0
 

Author Closing Comment

by:colinasad
ID: 37786993
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37787026
<<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).
0
 

Author Comment

by:colinasad
ID: 37787063
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37787078
Ok - I'll let you know tonight how accurate my memory is on this one...
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access on Mouse move 5 35
Access 2013 DoCmd.TransferSpreadsheet Not Working in Win 10 8 29
DCount Type Mismatch 2 22
Formatting Excel Data as Table from Access 11 13
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question