I've run my month-end invoice reports.  Each subtotal line is correct, but the grand total at the bottom of each column is off by a penny, one cent short of the correct total.  Help?

Quote:

Everything is money.   I do lots of addition to get the subtotal on an invoice.  Then, if a customer has a discount percentage, the following is done:

// Calculates the discount amount

Subroutine CalculateCustomerDiscount()
var vPosDiscAmt as Double
var vDiscountUnrounded as Double
IF Details_Customer_Discount_Percentage > 0
THEN
	{
	vPosDiscAmt = (@ToNumber(Subtotal) * (Details_Customer_Discount_Percentage/100))
	vDiscountUnrounded = (0 - vPosDiscAmt)
	Details_Discount_Amount = (@ToNumber(@Round(vDiscountUnrounded, 2)))
	// if @error then WriteLn("Error calculating discount amount")
	}
ELSE
IF (@ISBLANK(Details_Customer_Discount_Percentage) OR Details_Customer_Discount_Percentage = 0)
THEN
	{
	CLEAR(Details_Discount_Amount)
	IF (@ISBLANK(Details_Discount_Amount) AND Details_Customer_Discount_label = "CUSTOMER DISCOUNT")
	THEN
		{
		CLEAR(Details_Customer_Discount_label)
		}
	}
End Subroutine  

This is the only place rounding is done, and it seems to work well, but this must be where the problem lies.  The Invoice Total is not rounded; should it be?  What's odd is that all the subtotals are spot on, but it's the grand total that's off.

In looking at it I see that I've forced Details_Discount_Amount to "number" with ToNumber.  Should I whack it with ToMoney once it's been calculated?

Infinity wrote on Oct 1^st, 2007 at 7:42pm:


First thing you'll want to do is change them back to Money. Second thing you'll want to do is wait about a minute as Ben is currently typing up an answer that will explain why the grand total appears to be off by a penny, when in fact it's the right answer.

-Ray

Thanks Ben, I'll give it a try.  You guys ROCK!

Should I run this as a Mass Update on the values already saved in the database?

Details_Discount_Amount = @Round(vDiscountUnrounded, 2)) 

There's a wayward right paren at the end there, BTW. 

Quote:

I'm not using Subtotal anywhere that I can think of.  So, is all I have to do Mass Update

Invoice_Total = @ROUND(Invoice_Total, 2) 

or have I missed something?  That seems too easy. 

Done.  YAY!  I got my penny back!  I am so rich now!  Thanks Ben.

Ben, the code you gave me is doing funky stuff.  On an invoice with a subtotal of $100.00 and a discount of 5%, the discount amount becomes $10,307,921.51, for an invoice total of $10,308,021.51.

While my boss would like to see such an invoice going out I don't think the customer would be too happy.

I made the following changes and the numbers on the screen appear correct:

// Calculates the discount amount

Subroutine CalculateCustomerDiscount()
var vPosDiscAmt as Double
var vDiscountUnrounded as Money
IF Details_Customer_Discount_Percentage > 0
THEN
	{
	vPosDiscAmt = (@ToNumber(Subtotal) * (Details_Customer_Discount_Percentage/100))
	vDiscountUnrounded = (0 - vPosDiscAmt)
	Details_Discount_Amount = @Round(vDiscountUnrounded, 2)
	// if @error then WriteLn("Error calculating discount amount")
	}
ELSE
IF (@ISBLANK(Details_Customer_Discount_Percentage) OR Details_Customer_Discount_Percentage = 0)
THEN
	{
	CLEAR(Details_Discount_Amount)
	IF (@ISBLANK(Details_Discount_Amount) AND Details_Customer_Discount_label = "CUSTOMER DISCOUNT")
	THEN
		{
		CLEAR(Details_Customer_Discount_label)
		}
	}
End Subroutine 

I also put a statement elsewhere in my programming to round my grand total every time it is calculated.  I'll need to run some reports to see if this is working.  It's early in the day yet so I'll be experimenting for a while before you see this, but when you do, if there's something else I should be doing please let me know.  Thanks for your help.

Ben has changed the code that was posted yesterday.  I'll give that a try and report back.  Thanks for your help guys!

UPDATE: The modified code works perfectly.  WOO HOO!  You guys are the best.  THANKS!