Results 1 to 13 of 13
  1. #1
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30

    Sum totals in a report

    I have report that list a lot of items with a cost value. The report runs fine and gives me a list of items in stock with cost and in the table there is a Sold (yes/no) box.



    I placed a text box on the report to give me a cost summary of all items, but I want to capture the cost minus the sold items and still show the Yes/No box on the report.

    If I type in false on the criteria field in the query I get the correct value on the report but I can't show the Yes/No box with or without check marks.

    Can't figure this one out, any ideas?

    Jim

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    can't figure out what you actually have from your description. Can you post a screenshot of the report plus the report recordsource and the parameters you are using to open the report

  3. #3
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30

  4. #4
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30

    Report File

    Here is a screen shot of the Report.
    I tried to upload a screen shot but it didn't work.

    Report.zip

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    =-sum([puchaseprice]*[sold])

    where sold is the name of the checkbox you are pointing at

  6. #6
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30

    Not it

    Quote Originally Posted by Ajax View Post
    try

    =-sum([puchaseprice]*[sold])

    where sold is the name of the checkbox you are pointing at
    Your calculation is the difference between the Sum of all Sold (checked or not) and the Sum of only unchecked boxes.


    What I am looking for is the Sum of all unchecked boxes which I already have.
    The tricky part is to SHOW all Items sold or not (checked or unchecked) on the Report.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So put a control on the report that either holds the correct value or zero if not checked, and sum that. Control does not need to be visible.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30
    Ajax
    Welshgasman


    Ok, I think we got, by using Sum to calculate everything, the Report shows all Items sold or not and by combining that calculation with Ajax's calculation.


    I now have a Report showing all Items I have/had and a dollar value of only current items.


    This is the equation that works.
    Sum([Purchase Price]) - -Sum([Purchase Price]*[sold])


    Thanks guy's

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    which can be simplified to

    Sum([Purchase Price]* -(not [sold])

    or

    Sum([Purchase Price]* abs(not [sold])

  10. #10
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30
    Your right, just a little mod with = and a ) on the end. Works like a charm, Thank you!

    =Sum([Purchase Price]* -(not [sold]))

    =Sum([Purchase Price]* abs(not [sold]))

  11. #11
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30
    Can those be used to count only the items with no check in the box?

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    checkboxes are either true or false, -1 or 0. if you want a count, I would use a sum


    total sold=-sum([sold])

    total not sold=-sum(not [sold])

  13. #13
    JMZLR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    30
    Ajax

    Works like a charm. The calculations your using are something I haven't seen before. Like = - Sum

    Thank you again.

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

Similar Threads

  1. Sum totals and sub totals in report
    By Rogeman in forum Reports
    Replies: 1
    Last Post: 12-12-2018, 10:23 AM
  2. Replies: 1
    Last Post: 08-22-2018, 08:18 PM
  3. Replies: 8
    Last Post: 05-29-2015, 11:52 AM
  4. Report of just totals
    By TubbzUK in forum Reports
    Replies: 1
    Last Post: 02-12-2013, 11:15 PM
  5. Replies: 5
    Last Post: 12-06-2011, 11:18 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