459,918 Members | 1,662 Online
Need help? Post your question and get tips & solutions from a community of 459,918 IT Pros & Developers. It's quick & easy.

# Rounding Dollar Amounts

 P: n/a Hello Everyone, I'm using the following on a form in my database: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100 In this case, I want to calculate a total for an order. If the LineTaxExempt field is checked (meaning tax exempt), the total is figured by the ([Qty] field X [Price] field)+the [ShippingHandling] field. If the LineTaxExempt is not checked, then the above total, including shipping, is multiplied by 6% sales tax or 1.06. I then want this value rounded up to 2 decimal places. This control is a running total of the order. This works most of the time, but I have run into a few rounding errors. For example: With exempt checked 1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me \$34.35 instead of \$34.34 With tax exempt checked (and multiple lines) 2(Qty)X141.02(Price) + 0.00(ShippingHandling) 10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a grand total of \$323.25 instead of \$323.24 It's frustrating because it seems like it works part of the time and rounds wrong the other part of the time. By "rounds wrong", I mean I set it wrong to round. Any help would be greatly appreciated on how to modify this to give me the correct result. Thanks, -Chad Nov 7 '06 #1
6 Replies

 P: n/a Have you checked your field datatypes? Set [Qty] to Integer if possible and [Price] and [ShippingHandling] to Currency (2 places). I generally avoid using Single because of rounding issues; use Double instead. HTH sh***********************@yahoo.com wrote: >Hello Everyone,I'm using the following on a form in my database:=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])),((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100In this case, I want to calculate a total for an order. If theLineTaxExempt field is checked (meaning tax exempt), the total isfigured by the ([Qty] field X [Price] field)+the [ShippingHandling]field.If the LineTaxExempt is not checked, then the above total, includingshipping, is multiplied by 6% sales tax or 1.06. I then want thisvalue rounded up to 2 decimal places. This control is a running totalof the order.This works most of the time, but I have run into a few rounding errors.For example:With exempt checked1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me \$34.35instead of \$34.34With tax exempt checked (and multiple lines)2(Qty)X141.02(Price) + 0.00(ShippingHandling)10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me agrand total of \$323.25 instead of \$323.24It's frustrating because it seems like it works part of the time androunds wrong the other part of the time. By "rounds wrong", I mean Iset it wrong to round. Any help would be greatly appreciated on how tomodify this to give me the correct result.Thanks,-Chad -- Message posted via http://www.accessmonster.com Nov 7 '06 #2

 P: n/a sh***********************@yahoo.com wrote: Hello Everyone, I'm using the following on a form in my database: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100 In this case, I want to calculate a total for an order. If the LineTaxExempt field is checked (meaning tax exempt), the total is figured by the ([Qty] field X [Price] field)+the [ShippingHandling] field. If the LineTaxExempt is not checked, then the above total, including shipping, is multiplied by 6% sales tax or 1.06. I then want this value rounded up to 2 decimal places. This control is a running total of the order. This works most of the time, but I have run into a few rounding errors. For example: With exempt checked 1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me \$34.35 instead of \$34.34 With tax exempt checked (and multiple lines) 2(Qty)X141.02(Price) + 0.00(ShippingHandling) 10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a grand total of \$323.25 instead of \$323.24 It's frustrating because it seems like it works part of the time and rounds wrong the other part of the time. By "rounds wrong", I mean I set it wrong to round. Any help would be greatly appreciated on how to modify this to give me the correct result. Thanks, -Chad Here's a shot in the dark. Instead of: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100 try sliding a right parenthesis to the left: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling]))*1.06))*100)))/100 James A. Fortune CD********@FortuneJames.com Nov 7 '06 #3

 P: n/a CD********@FortuneJames.com wrote: sh***********************@yahoo.com wrote: Hello Everyone, I'm using the following on a form in my database: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100 In this case, I want to calculate a total for an order. If the LineTaxExempt field is checked (meaning tax exempt), the total is figured by the ([Qty] field X [Price] field)+the [ShippingHandling] field. If the LineTaxExempt is not checked, then the above total, including shipping, is multiplied by 6% sales tax or 1.06. I then want this value rounded up to 2 decimal places. This control is a running total of the order. This works most of the time, but I have run into a few rounding errors. For example: With exempt checked 1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me \$34.35 instead of \$34.34 With tax exempt checked (and multiple lines) 2(Qty)X141.02(Price) + 0.00(ShippingHandling) 10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a grand total of \$323.25 instead of \$323.24 It's frustrating because it seems like it works part of the time and rounds wrong the other part of the time. By "rounds wrong", I mean I set it wrong to round. Any help would be greatly appreciated on how to modify this to give me the correct result. Thanks, -Chad Here's a shot in the dark. Instead of: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100 try sliding a right parenthesis to the left: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling]))*1.06))*100)))/100 James A. Fortune CD********@FortuneJames.com Thanks to everyone for the help. This is the info for my 3 fields: QTY ------- Field: Double Format: Standard Decimal Places: 2 Price ---------- Format: Currency Decimal Places: 4 ShippingHandling ------------------------------- Format: Currency Decimal Places: 2 I tried setting QTY to Integer/Auto-Decimal, Price and Shipping handling to Currency/2 decimal places. I still had rounding errors some of the time. I really need 4 decimal places on price since some unit prices require 4. I also tried moving the parenthesis, and still had rounding errors part of the time. Is there a better way to round up to 2 decimal places other than the =-Int(-Sum([Fieldname]) * 100) / 100 method that will be right? Thanks, -Chad Nov 8 '06 #4

 P: n/a Hi Chad, I think you have to look at your rounding procedure. Int of a negative number returns the next lower integer: -Int(-9.2) wil give 10 So, -Int(-9.2) + (-Int(-9.2)) = 20, instead of 18 as expected. A better way to round (i.e. 9.2 gives 9 and 9.7 gives 10) is: rounded_value = Int(original_value + 0.5). Or, when you work dollars (or euro's): rounded_value = Int(100 * original_value + 0.5)/100. Remember in this case that multiplication is done before addition! Your running total should be based on these rounded values. Regards, HBinc. sh***********************@yahoo.com schreef: Hello Everyone, I'm using the following on a form in my database: =(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])), ((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100 In this case, I want to calculate a total for an order. If the LineTaxExempt field is checked (meaning tax exempt), the total is figured by the ([Qty] field X [Price] field)+the [ShippingHandling] field. If the LineTaxExempt is not checked, then the above total, including shipping, is multiplied by 6% sales tax or 1.06. I then want this value rounded up to 2 decimal places. This control is a running total of the order. This works most of the time, but I have run into a few rounding errors. For example: With exempt checked 1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me \$34.35 instead of \$34.34 With tax exempt checked (and multiple lines) 2(Qty)X141.02(Price) + 0.00(ShippingHandling) 10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a grand total of \$323.25 instead of \$323.24 It's frustrating because it seems like it works part of the time and rounds wrong the other part of the time. By "rounds wrong", I mean I set it wrong to round. Any help would be greatly appreciated on how to modify this to give me the correct result. Thanks, -Chad Nov 8 '06 #5

 P: n/a sh***********************@yahoo.com wrote: Thanks to everyone for the help. This is the info for my 3 fields: QTY ------- Field: Double Format: Standard Decimal Places: 2 Price ---------- Format: Currency Decimal Places: 4 ShippingHandling ------------------------------- Format: Currency Decimal Places: 2 I tried setting QTY to Integer/Auto-Decimal, Price and Shipping handling to Currency/2 decimal places. I still had rounding errors some of the time. I really need 4 decimal places on price since some unit prices require 4. I also tried moving the parenthesis, and still had rounding errors part of the time. Is there a better way to round up to 2 decimal places other than the =-Int(-Sum([Fieldname]) * 100) / 100 method that will be right? I don't use the Currency type in calculations much. Here's another to try: 'Begin Module Code Public Function RoundUpCents(x As Double) As Currency x = x * 100 RoundUpCents = CCur((Int(x) + Abs(x

 P: n/a I suggest you use double for all of your numeric field types. Do you think that it will require too much overhead? Whenever I've ever mixed numeric data types in calculations, I've found rounding errors. You can write your own rounding function, but I think you agree that this would be an "ugly" solution. sh***********************@yahoo.com wrote: Hello Everyone, [quoted text clipped - 49 lines] >James A. FortuneCD********@FortuneJames.com Thanks to everyone for the help. This is the info for my 3 fields:QTY-------Field: DoubleFormat: StandardDecimal Places: 2Price----------Format: CurrencyDecimal Places: 4ShippingHandling-------------------------------Format: CurrencyDecimal Places: 2I tried setting QTY to Integer/Auto-Decimal, Price and Shippinghandling to Currency/2 decimal places. I still had rounding errorssome of the time. I really need 4 decimal places on price since someunit prices require 4.I also tried moving the parenthesis, and still had rounding errors partof the time.Is there a better way to round up to 2 decimal places other than the =-Int(-Sum([Fieldname]) * 100) / 100 method that will be right?Thanks,-Chad -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200611/1 Nov 9 '06 #7

### This discussion thread is closed

Replies have been disabled for this discussion.