Subtotal line doesn't equal the total in the unit field (1 Viewer)

MD69

New member
Local time
Today, 01:01
Joined
Feb 28, 2013
Messages
5
I'm using a customised version of one of the default databases supplied with Office 2010. A customer has overpaid an invoice today by £0.01. I'm not bothered about being overpaid (who is!) but it's the way that the values have been calculated that's puzzling me. The "Order Details" jpeg shows the product ordered, quantity & price. The "discount" field is hidden but has a value of 15% entered.

The Invoice shows the product line as having a value of £0.01 more than the order detail but the Subtotal line shows the correct value from the order detail. The coding on the Invoice report is

Item line: =([Quantity]*[UnitPrice])-(CLng([Quantity]*[UnitPrice]*[Discount]*100)/100)
Subtotal line: =Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100)

I've not amended those from the original database.

I'm guessing it's to do with how the discount is calculated on the item line.

Can anyone suggest how the coding on the Item line should be amended so that if displays the info correctly please?
 

Attachments

  • Invoice.pdf
    5.9 KB · Views: 294
  • Order details.jpg
    Order details.jpg
    118 KB · Views: 281

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:01
Joined
Jan 23, 2006
Messages
14,318
Did you try Currency datatype? Instead of CLng try CCur
 

MD69

New member
Local time
Today, 01:01
Joined
Feb 28, 2013
Messages
5
Did you try Currency datatype? Instead of CLng try CCur
I didn't, I have now, and it's worked! Many thanks for such a speedy response!
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:01
Joined
Feb 19, 2002
Messages
36,296
I always use the currency data type when I want to avoid floating point errors. Currency is a scaled integer with four decimal places although you can hide/show them or not. What this means is that 100.00 is stored as 1000000 and the decimal point is assumed to be to the left of the fourth digit from the right. This means that all calculations are done with integer math and rounded correctly.

So whether the field is actually currency or not, I use the currency data type as long as I don't need more than four positions of decimal precision. If it is a percent or just not currency, the format property handles how it should look.

There is an interesting article at www.FMSINC.com called "When Access Math doesn't add UUp" which explains why we see this type of error for single and double precision data type fields. Single and double are binary but currency is decimal. Binary does not always convert cleanly to decimal.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:01
Joined
Sep 12, 2006
Messages
14,835
You can always get rounding differences though. Even though the currency data type is good for 4dps, we actually buy and sell in pennies, rather than fractions of a penny.

For instance, if you have an invoice with 8 items, and you evaluate VAT at 20% of the total, you may well get a different result if you calculate the VAT on each line, and sum the total, compared with summing the lines, and working out the VAT on the invoice total, depending on how you round the values.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:01
Joined
Feb 19, 2002
Messages
36,296
That is true no matter what data type you use. I was talking about floating point errors which occur ONLY when you are using floating point data types and never with currency.
 

Users who are viewing this thread

Top Bottom