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  
Share this Question
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))))/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

Message posted via http://www.accessmonster.com  
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  
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/AutoDecimal, 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  
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
 
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/AutoDecimal, 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 <Int(x))) / 100#)
End Function
'End Module Code
James A. Fortune CD********@FortuneJames.com  
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. 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/AutoDecimal, 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

Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200611/1   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 1975
 replies: 6
 date asked: Nov 7 '06
