none
Access 2013 Datasheet Total Row Shows no Results for SUM Function - Bug RRS feed

  • Question

  • A updatable subform which links to an SQL table is shown in datasheet view in Access 2013. The Totals row has been enabled. The subforms rowsource is set via vba as Table.* and the form has a handful of objects to provide filters for the subform.

    The table consists of fields of both nvarchar and money datatypes.  The data can be group by, let's call it "an order".

    For one order the Totals row works and shows the SUM of the above corresponding column; yet for another order the SUM doesn't return any value (but average, count and the other functions do).  At first I thought there was a problem with the data in the column(s) to be calculated, but there wasn't.  All were valid, numeric, non-null values.

    Here's where it gets really weird.

    I then started comparing an older db with the same data and found that a couple of the text fields had been updated by a user, so one by one I set the changed fields back to NULL for those records where the user had added information.  Of the 4 fields that had been updated, setting all of the fields for 2 of them to NULL didn't fix the issue; but, no so with the other 2 fields.

    Here's what I found:  if either of these two fields (both nvarchar(255) datatype fields) contain any non-NULL data, then the Totals SUM function shows nothing.  If both are updated to NULL, then the SUM returns the proper calculation/result.

    Oh, and the SUM fails for all numeric columns in the datasheet, not just one.

    I went back and forth, Nulling the fields and adding back in values (even blanks) and every time the Totals row SUM function would show nothing if either of these two fields had even a single record with wasn't null.

    Does anyone know if there is a patch or fix to this issue?

    Thanks in advance.

    Thursday, October 8, 2015 7:27 PM

All replies

  • Hi Siprios,

    >> if either of these two fields (both nvarchar(255) datatype fields) contain any non-NULL data, then the Totals SUM function shows nothing.  If both are updated to NULL, then the SUM returns the proper calculation/result.

    As far as I know, the total row is supported for Number, Decimal or Currency, and it is not supported for nvarchar. Did you mean sum function failed on numeric conlumns when there is null in non-numeric column? I made a test with this situation, but I failed to reproduce your issue.

    It would be helpful if you could share us a screen shot about table design in Access 2013, and a screen shot about your datasheet view with a total row.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, October 9, 2015 2:31 AM
  • In the image below on the Totals row, the ITD Unob Comm column is set to use the SUM function, while the ITD Uncosted FI Obs is using the Average function (just to show that the Total row works).  See the FY FF column?  If I remove the 'Yes' value in the FY FF column, then the SUM function works in the ITD Unob Comm column.  The two value columns are datatype money.  There's another text column not shown that has the same affect on the Total row, but clearly not every text column has that affect.  I cannot determine the relationship between these two text fields and the Total row problem. B

    Here you can see that all of the functions appear in the drop-down, but only SUM fails to return a value.

    • Edited by Siprios Monday, October 19, 2015 1:23 PM
    Monday, October 19, 2015 1:20 PM
  • Hi Siprios,

    I could not reproduce your issue.

    I suggest you recreate the table with the same design, and test this again.

    If you could reproduce this issue with a new table, it would be helpful if you could share us the simple database through OneDrive.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 20, 2015 5:22 AM
  • Just had a similar problem.  This may help you too.  It's certainly quicker than rebuilding the form which is what I had to do last time.

    I have a form that has a datasheet subform embedded in it.  (I do it that way so I can have search boxes on my main form that apply a custom filter to my datasheet).  The datasheet has totals (Sum) and everything has been working fine, and I'm continuing to develop this particular database application.

    Cause of the Problem (mine anyway) ...

    Then today, after making some changes to the datasheet's "Record Source" SQL (I renamed a column) and as a result my Sum totals appear blank (as I discovered later).  What I thought I'd done, was also rename the textbox "Control Source" to match the new column name, but I hadn't.  When opening in the form, it silently failed (so I had no idea of the problem cause) and the Sum's at the bottom of the columns were blank.

    Solution...

    I went into the datasheet's design view and noticed that little green marker that appears when a textbox has an invalid Control Source name.  I corrected this, saved the datasheet and reopened in normal form view.  Hey Presto!  The Sum figures were back.

    Hope this helps - a bit late maybe but can easily happen again!

    Wednesday, January 25, 2017 4:25 PM
  • Try saving the form as text, (application.saveastext acform, "formname","filename.txt") then removing all occurences of aggregatetype=0,1 or 2 then reload form application.loadfromtext .... Totals row should now work.
    Tuesday, May 7, 2019 11:39 AM