Rounding Dollar Amounts

[shaqattack1992-newsgroups]

Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*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 [ShippingHandlin g]
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(Pr ice) + 6.44(ShippingHa ndling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02(P rice) + 0.00(ShippingHa ndling)
10(Qty)X4.12(Pr ice) + 0.00(ShippingHa ndling) 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

[kingston via AccessMonster.com]

Have you checked your field datatypes? Set [Qty] to Integer if possible and
[Price] and [ShippingHandlin g] to Currency (2 places). I generally avoid
using Single because of rounding issues; use Double instead. HTH

sh************* **********@yaho o.com wrote:

>Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*1.06))*100))) )/100

In this case, I want to calculate a total for an order. If the
LineTaxExemp t field is checked (meaning tax exempt), the total is
figured by the ([Qty] field X [Price] field)+the [ShippingHandlin g]
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(P rice) + 6.44(ShippingHa ndling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02( Price) + 0.00(ShippingHa ndling)
10(Qty)X4.12(P rice) + 0.00(ShippingHa ndling) 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

[CDMAPoster]

sh************* **********@yaho o.com wrote:

Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*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 [ShippingHandlin g]
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(Pr ice) + 6.44(ShippingHa ndling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02(P rice) + 0.00(ShippingHa ndling)
10(Qty)X4.12(Pr ice) + 0.00(ShippingHa ndling) 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])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*1.06))*100))) )/100

try sliding a right parenthesis to the left:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g]))*1.06))*100)) )/100

James A. Fortune
CD********@Fort uneJames.com

[shaqattack1992-newsgroups]

CD********@Fort uneJames.com wrote:

sh************* **********@yaho o.com wrote:

Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*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 [ShippingHandlin g]
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(Pr ice) + 6.44(ShippingHa ndling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02(P rice) + 0.00(ShippingHa ndling)
10(Qty)X4.12(Pr ice) + 0.00(ShippingHa ndling) 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])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*1.06))*100))) )/100

try sliding a right parenthesis to the left:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g]))*1.06))*100)) )/100

James A. Fortune
CD********@Fort uneJames.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

ShippingHandlin g
-------------------------------
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

[hbinc]

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_va lue + 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************* **********@yaho o.com schreef:

Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandlin g])),
((([Qty]*[Price])+[ShippingHandlin g])*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 [ShippingHandlin g]
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(Pr ice) + 6.44(ShippingHa ndling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02(P rice) + 0.00(ShippingHa ndling)
10(Qty)X4.12(Pr ice) + 0.00(ShippingHa ndling) 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

[CDMAPoster]

sh************* **********@yaho o.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

ShippingHandlin g
-------------------------------
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 <Int(x))) / 100#)
End Function
'End Module Code

James A. Fortune
CD********@Fort uneJames.com

[kingston via AccessMonster.com]

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************* **********@yaho o.com wrote:

Hello Everyone,

[quoted text clipped - 49 lines]

>James A. Fortune
CD********@Fort uneJames.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

ShippingHandli ng
-------------------------------
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

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1