Results 1 to 2 of 2

How to have a total by summarizing the face value for each line in invoice query?

  1. #1
    calvinyoo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019

    How to have a total by summarizing the face value for each line in invoice query?

    How to have a "correct" total by summarizing the value for each line in invoice query?

    line 1 subtotal = $123.51 (but actual value is 123.514)
    line 2 subtotal = $123.43 (but actual value is 123.434)
    line 3 subtotal = $123.88 (but actual value is 123.884)
    line 4 subtotal = $123.99 (but actual value is 123.994)
    line 5 subtotal = $123.77 (but actual value is 123.774)

    Because of decimal value in currency mode, we will get subtotal as above mentioned, eg $123.51. If we set a SUM in report footer, we will get $618.60 in total.
    But, in paper printing, we will think suppose to have the total $618.58 . There is difference $0.02 .

    Please help what can I do with Query or Report here?

    I tried some steps like put it as Fixed / General Number , or even change decimal , but could not solve this.
    Thanks in advance!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    You tried those fixes where?
    If your table field is single or double data type set to 3 places and your query is a Totals query using Sum, and that field is set to Currency (query property sheet) and the decimal count is set to 3 in the query, I would think it ought to work. Warning - if you switch to single or double in the table from some other type, you might introduce floating point errors so only play with a copy of your table.

    If you find that works, you can always update your "fixed" table (assuming you've altered the number type) using your original table via update query but I would even perform the update on a test copy first.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

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

Similar Threads

  1. Replies: 11
    Last Post: 09-13-2018, 02:51 AM
  2. Replies: 9
    Last Post: 11-26-2017, 10:14 AM
  3. Replies: 3
    Last Post: 09-23-2017, 08:16 AM
  4. Sub total and total on invoice form
    By knh2r in forum Access
    Replies: 3
    Last Post: 07-13-2016, 02:09 PM
  5. Add New Line Item Detail to Invoice Based on Status
    By breakingme10 in forum Programming
    Replies: 2
    Last Post: 06-23-2014, 04:04 PM

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
Tech Forums: Microsoft Office Forums