By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,918 Members | 1,662 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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))))/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

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

James A. Fortune
CD********@FortuneJames.com

Nov 8 '06 #6

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/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.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.