Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58

    Sum Calculated Text Box in Report Header Receiving Error Access 2010

    Hi all,
    I am having a problem with summing a text box value in an Access 2010 report page header.


    The text box I am summing is a calculated text box named [Total Pricing] and has the following expression and is working fine:


    =[Cost Packet Quantity]*[Unit Pricing]

    I am trying to sum these [Total Pricing] boxes in the report header with another text box and the expression and I am receiving an error:

    =Sum([Total Pricing])

    I keep receiving #Error in the field when I run the report.
    The format is set to currency.
    Running sum is set to overall.

    Thanks for your help.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use the original field names for your sum

    Code:
    =Sum([Cost Packet Quantity]*[Unit Pricing])
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Sorry, that did not work either.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    What is the exact error you get and in which part of the report are you placing the control?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    The error is "#Error" in my field when I run the report.
    The field is in the Page Header portion of the report.
    I have tried it in the Page Footer without success either.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You do still have the original textbox in the detail section I hope!
    Code:
    =[Cost Packet Quantity]*[Unit Pricing]
    Then this should be in the report footer or a group footer i.e. after the detail

    Code:
    =Sum([Cost Packet Quantity]*[Unit Pricing])

    If that doesn't solve it, please upload the report in design view together with any other useful info e.g. report SQL ; report grouping info
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Yes, it is there, and I did try it in other sections of the report with no success. Hopefully this is what you need for the design view.
    Doc2.pdf

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. You have the sum in the Page Header i.e. before the detail. Move it to the Report Footer so it can add all the values
    2. You have not modified the expression for the sum. As already explained, change it to:
    Code:
    =Sum([Cost Packet Quantity]*[Unit Pricing])
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    I did try it as already explained. And in every section of the report. Both with my expression and the one you suggested. All to no avail. One thing I did notice was that when the fields are in the footer, and I run the report, it prompts me to enter a value for the Unit Price. Unit Price has this expression:
    Code:
    =IIf([Ordr Total]>[Price Break4],([Price Break4 Cost]*[Ordr Total]+[Shipping Charges])/[Ordr Total],[Cost])
    I triple checked for any typing errors making sure control sources were correct. Everything looks fine.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Is 'Ordr' a typo here or in your report?

    The TRUE part of your IIf statement is unclear.
    Try adding additional brackets or add both items then divide the sum by Order Total
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    No, 'Ordr" is not a typo. (long story). Additional brackets did not work. Now it is prompting me for the 'Total Price'. If I take out the expressions for Unit Pricing and Total Pricing and replace them with simple values it runs fine.

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Remove ALL non-unbound names from ALL expressions - I think that is what ridders has been trying to tell you. Use ONLY names that come from your record source.

  13. #13
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    That will be fine for the 'Unit Price'. But not for the 'Total Price'. 'Total Price' needs the 'Unit Price' for its calculations. Would it be better then to stick these calculations in a Query and draw them out from there?

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not true. Total Price does NOT need Unit Price. It is just a calculation. You can do it in a query, or you can do it with an expression, either way.

  15. #15
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Ok, I think I am misunderstanding what you mean by 'expression' versus just a 'calculation'. And Total Price uses Unit Price in its calculation so how can it not need it? Unless you are refering to Total Material Cost which is just summing the Total Prices?

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

Similar Threads

  1. Replies: 1
    Last Post: 08-03-2017, 08:17 PM
  2. Replies: 1
    Last Post: 08-11-2015, 11:33 AM
  3. Replies: 4
    Last Post: 11-10-2014, 09:47 AM
  4. Replies: 6
    Last Post: 06-27-2013, 12:38 PM
  5. Receiving Error when creating Calculated Field
    By hydrojoe11 in forum Access
    Replies: 4
    Last Post: 02-04-2013, 08:01 PM

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