Rounding Dollar Amounts

[chaddiesel]

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.

Is there a better formula to use in a form or query to round up to 2 decimal places other than the
=-Int(-Sum([Fieldname]) * 100) / 100 method that will give me the right results?

Any help/examples would be greatly appreciated on how to
modify this to give me the correct result each time.

Here are my field types:

QTY
-------
Field: Double
Format: Standard
Decimal Places: 2

Price
----------
Format: Currency
Decimal Places: 4 (some unit prices are 4 digits--Example $1.2525)

ShippingHandling
-------------------------------
Format: Currency
Decimal Places: 2

Thanks,

-Chad

 

[boblarson]

You could try using the Round function: Round(NumberToBeRounded,NumberOfDecimalPlaces)

I don't know if you can use it in a query, per se, but you can use it in VBA, so if you need to you could put it on the AfterUpdate event of the text box to change it.

 

[uplate]

Three thoughts:

1) Why the double negative? Why (-Int(-Sum(... instead of (Int(Sum(... ? Wouldn't it work exactly the same without double negation?

2) It's not required, but your code would be more readable, maintainable, (and possibly less error-prone) if you included as little as possible in your conditional:

try:

=Int(Sum([Qty]*[Price]+[ShippingHandling])*IIf([LineTaxExempt]=Yes,100,106))/100

instead of:

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


3) If some of your unit prices have four decimal places, then don't you want to round to the nearest cent (i.e. rounding .5 up)? If so, your formula needs to explicitly do that. The following trick of adding 0.5 before truncating is commonly employed for this type of rounding:

=Int( blah * 100 + 0.5 ) / 100

In that case, try:

=Int(Sum([Qty]*[Price]+[ShippingHandling])*IIf([LineTaxExempt]=Yes,100,106)+0.5)/100

 

[gemma-the-husky]

just a thought

Hiw are you doing ....

1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me $34.35
instead of $34.34

i don't see how access can possibly give you this result with normal; arithmetic. Any rounding here must be infitesimal, and certainly not enough to give you a whole cent extra