Subtotal line doesn't equal the total in the unit field

[MD69]

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?

 

[jdraw]

Did you try Currency datatype? Instead of CLng try CCur

 

[MD69]

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!

 

[jdraw]

You are very welcome. You may also review this for reference.

 

[gemma-the-husky]

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.