Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Sum yields #Error in form footer

    I'm not sure what is causing this, but I have many forms that sum fields in detail section of the form. From time to time, the sum would yield #Error. I would then manipulate by compacting, deleting the cells, copy from a different form that has no issues, or other actions. For the most part, the sum would then work. However, my luck has run out with this particular form. I basically copy from a form that worked and removed a field in the query and deleted it the same field from the form. See Pics below (one works and one does not). However, whatever I do, I cannot get that #Error to go away. Any ideas on how I can resolve my issues?



    Click image for larger version. 

Name:	Sum Error.jpg 
Views:	21 
Size:	98.1 KB 
ID:	35143Click image for larger version. 

Name:	Sum No Error.jpg 
Views:	20 
Size:	105.5 KB 
ID:	35144

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If the fields are set to =Sum(field),
    then it should never give Err.

    is this what u are using?

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    very confused by your description

    but I have many forms that sum fields in detail section of the form
    please clarify - summing on a form is usually done in the header or footer

    deleting the cells
    excel has cells, databases have columns and rows - which is it?

    From time to time, the sum would yield #Error
    implies the error is actually in one or more of the rows - so any calculations in your rows are suspect

    since the problem is to do with calculations, we can't help without seeing those calculations - both for rows and totals

    Not sure if it is relevant but if you have ** in your field names, that can cause issues

  4. #4
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by ranman256 View Post
    If the fields are set to =Sum(field),
    then it should never give Err.

    is this what u are using?
    Well, my formula is actually =round(Sum[Field],0), which works in other forms. I just copy the entire row and paste it over. I even recreate a textbox and rewrite the expression. Still no luck.

  5. #5
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Ajax View Post
    very confused by your description

    please clarify - summing on a form is usually done in the header or footer

    excel has cells, databases have columns and rows - which is it?

    implies the error is actually in one or more of the rows - so any calculations in your rows are suspect

    since the problem is to do with calculations, we can't help without seeing those calculations - both for rows and totals

    Not sure if it is relevant but if you have ** in your field names, that can cause issues
    Yes, the summing is in the footer of the form.
    I am summing rows of data. The images I included above show one form with no issues, and the other with issues. The only difference is that I removed a field, "Category"
    There's data in every row, including 0's, so there's no error in any rows.
    I do not have any ** in any field names.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The Field being summed in the errant Forms isn't a calculated Field, is it?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Missinglinq View Post
    The Field being summed in the errant Forms isn't a calculated Field, is it?

    Linq ;0)>
    The fields are summed. See Image.
    Click image for larger version. 

Name:	Sum Error Query.jpg 
Views:	14 
Size:	59.7 KB 
ID:	35156

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    did your category field have a criteria? If so, it may be that you are now bringing data through which is excluded in the 'working' form - and perhaps there is something wrong with that data...

    Why do you have the tblCustomerListReporting table - you are not using it?

    Also, small point, but you should be applying the criteria to a WHERE column, not a GROUP BY column. HAVING should only be applied to columns that are aggregated. As structured at the moment, you are bringing all data through and then applying a criteria - so a much slower query.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Double check that the sum textboxes are in the form footer, not the page footer.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Ajax View Post
    did your category field have a criteria? If so, it may be that you are now bringing data through which is excluded in the 'working' form - and perhaps there is something wrong with that data...

    Why do you have the tblCustomerListReporting table - you are not using it?

    Also, small point, but you should be applying the criteria to a WHERE column, not a GROUP BY column. HAVING should only be applied to columns that are aggregated. As structured at the moment, you are bringing all data through and then applying a criteria - so a much slower query.
    Yes, the one that works contains the category field. It does have a criteria. As far as I can tell, there's no issue with the data. It's essentially the same data but without the Category field.
    tblCustomerListReporting is primarily for additional criteria. See image.Click image for larger version. 

Name:	Sum Error Query2.jpg 
Views:	9 
Size:	51.0 KB 
ID:	35180

  11. #11
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by pbaldy View Post
    Double check that the sum textboxes are in the form footer, not the page footer.
    Yup. The sum textboxes are in the footer. See image.
    Click image for larger version. 

Name:	Sum Error Query3.jpg 
Views:	9 
Size:	108.9 KB 
ID:	35181

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I get that error sometimes when I change the names of controls. If I have a control with the same name as a field, then change the name of the control, access changes all the other references to that control instead of the field which screws up footer calcs for some reason. Maybe look into that?

  13. #13
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by kd2017 View Post
    I get that error sometimes when I change the names of controls. If I have a control with the same name as a field, then change the name of the control, access changes all the other references to that control instead of the field which screws up footer calcs for some reason. Maybe look into that?
    Just checked. All the controls being summed have the same name as the fields. I did not change any of them.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There's a 1 at the end of each field name in the footer.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    OK. I just discovered the problem. In the circled field below, I noticed the expression is still referencing the field Category, which I removed, the expression was "=Count([Category])". I changed it to "=Count([BudgetLine])" and now it works. It is strange that since Category is not in the query, upon opening the form, I did not get some kind of error message saying that Category cannot be found. Thank you all for helping.

    Click image for larger version. 

Name:	Sum Error Query4.jpg 
Views:	8 
Size:	77.6 KB 
ID:	35182

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 10-10-2016, 06:07 AM
  2. Replies: 8
    Last Post: 04-29-2013, 12:11 PM
  3. Replies: 1
    Last Post: 12-14-2012, 12:32 AM
  4. Replies: 11
    Last Post: 08-27-2012, 11:26 AM
  5. My report yields more than one result
    By hawzmolly in forum Reports
    Replies: 16
    Last Post: 07-04-2009, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums