Results 1 to 6 of 6
  1. #1
    krag is offline Medium experience
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    71

    Sum function by expression builder giving #ERROR

    I have been developing fairly complicated projects using a variety of techniques since several years and working well with a small-sized client user.


    Recently upgraded to Microsoft 365 Apps for Enterprise and not sure this is a reason for the problem.
    It is very straightforward.
    I am developing a new project now.
    In one continuous form with no sub-forms, have a unbound Sum field in the footer for a field which is from the form recordsource table via a query (for sorting some fields).
    I have used similar ones in many previous projects without any problem.
    Even if some of the record values for that field are null, it has worked.
    When I run a sum query directly, it shows sum correctly.
    But in the form footer, it simply shows #ERROR.
    It seems so simple but it is not working as it should.
    Incidentally the older projects where similar thing has been used are also running on the new MS365 as above but there is no problem (sum is coming up correctly).
    Any ideas?
    Thanks in advance

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    What expression are you using in the footer textbox
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    krag is offline Medium experience
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    71
    Expression in footer text box is:
    =Sum([McTimeP])
    McTimeP is a text box in the continuous form for simple values in decimal numbers and it is a field from the recordsource of the form.
    It get its value either directly entered in the form by user or through VBA setting or may be null.
    I have 2 such fields in this form and need to sum them up individually first and then show a grand total as well at the bottom.
    Both are showing same error.
    There is one more field also but right now is not from the recordsource as it is purely a calculated one within each record.
    However, if necessary I plan to add it into the recordsource as well.


    In another older project, a very similar action is done in fact within a sub-form without any problem even now.

    I just now tried with a simple form having only this field McTimeP from the same recordsource, without any other fields.
    With the same expression text box in footer, it worked correctly showing the sum (even if some records were null).
    Looks like the problem is elsewhere in the form.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by krag View Post
    Expression in footer text box is:
    =Sum([McTimeP])
    McTimeP is a text box in the continuous form for simple values in decimal numbers and it is a field from the recordsource of the form.
    It get its value either directly entered in the form by user or through VBA setting or may be null.
    I have 2 such fields in this form and need to sum them up individually first and then show a grand total as well at the bottom.
    Both are showing same error.
    There is one more field also but right now is not from the recordsource as it is purely a calculated one within each record.
    However, if necessary I plan to add it into the recordsource as well.


    In another older project, a very similar action is done in fact within a sub-form without any problem even now.

    I just now tried with a simple form having only this field McTimeP from the same recordsource, without any other fields.
    With the same expression text box in footer, it worked correctly showing the sum (even if some records were null).
    Looks like the problem is elsewhere in the form.
    Is it possible for you to post a copy of your db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try DSum on the field rather than the name of the control. If your control and field have the same name, then AFAIC, this is another reason why I don't allow that. You're expecting Access to default to one or the other, and the summation values are really in the field, not in one control that is replicated many times in a datasheet or continuous form. You might be OK with DSum if the field and control have the same name but I don't run into that situation as a rule.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    krag is offline Medium experience
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    71
    Hi Bob and Micron
    Thanks for your ideas.
    Happy to inform problem is resolved now as below.
    As I mentioned earlier, there are 3 fields in the form which need sums.
    2 of them are bound to a recordsource query / table while 1 is unbound.
    I had made sum controls in the footer for each of the fields above.
    As expected, unbound sum gave #ERROR.
    Problem was the other 2 also giving #ERROR inspite of being bound sums.
    When I removed the unbound sum control, to my pleasant surprise, both bound sum controls started working correctly.
    Though it is a puzzle why it should be so.

    I will mark this thread as solved.
    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use custom function in Expression Builder
    By MattLewis in forum Programming
    Replies: 3
    Last Post: 01-17-2018, 10:54 PM
  2. Expression Builder - Function DateDiff
    By geraldk in forum Access
    Replies: 2
    Last Post: 04-01-2015, 12:08 PM
  3. Control source Expression giving #Error
    By N3w2access in forum Forms
    Replies: 5
    Last Post: 01-21-2014, 07:53 AM
  4. iff function in expression builder
    By deso in forum Queries
    Replies: 4
    Last Post: 03-21-2013, 11:20 PM
  5. IIf Function for expression builder
    By Bertrand82 in forum Programming
    Replies: 3
    Last Post: 11-12-2012, 12:34 AM

Tags for this Thread

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