Rounding totals

[sswedeen]

The problem. I am using a query to figure tax, order totals and balance due for my invoices.
For example. If the tax is 4.81 even, but I round the expression, the tax would then be 4.82. Which is not correct.
If I use the Sum expression for figuring tax then I have a tax total of 2.405 which is rounded to 2.41. Which is correct.
The problem is
When I use the sum expression I get something like this:

The product total is 37.00
Tax 2.405 Rounded to 2.41
Total 39.405 Rounded to 39.41
Amount Paid 39.41
Balance due is (0.01)

However when I use the Round Expression the above problem is corrected but another problem develops.

Heres the problem when Round expression is used.

Product total 132
tax 8.59 But should be 8.58even
Total 140.59
Amount paid 140.58
Balance 0.01
This expression would be correct if the Sum expression was used.

I am using Access 2003 if that makes any difference.

Any Ideas?

 

[genomon]

I am unable to credit the original author from the 'net as that information was not supplied (maybe Ken Getz?), but we use this user-defined function, and works quite well:
Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double

' : 0.5 is rounded up
'Parameters : dblNumber - number to round
' : intDecimals - number of demal places
' to round to

' : (positive for right of decimal, negative for left
'Returns : Rounded number

Dim dblFactor As Double
Dim dblTemp As Double ' Temp var to prevent rounding problems in INT()

dblFactor = 10 ^ intDecimals
dblTemp = dblNumber * dblFactor + 0.5
RoundTotal = Int("" & dblTemp) / dblFactor

End Function

Good luck!

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[sswedeen]

Thanks, Although I have to admit I'm not savvy on writing the expression.

Would I write it like this?

Total: RoundTotal([Order Total],0.05,2)

Obviously I have alot to learn.

 

[lespaul]

I think it would be:

RoundTotal([Order Total], 2)

Or you could do:

SELECT [Order Total], RoundTotal([Tax], 2), [Order Total] + RoundTotal(Tax, 2) As RoundTotal From.....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[genomon]

Looks good to me, but maybe change the alias in the SELECT from As RoundTotal to AS RondedTotal or something to avoid confusion?...

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[barny2006]

you need a tax table to calculate tax for the whole dollars plus fractions. like this:

0=.10 no tax
.11-.25 1 cent
.26-.40 2 cents
.41-.55 3 cents
.56-.70 4 cents
.71-.85 5 cents
.86-.99 6 cents.

 

[sswedeen]

Thank all for your help. I finally got the problem taken care of.
I used the advise from genomon and from Lespaul.
I placed the code from genomon into the Global Code under general declarations. Then went into my Query and found the RoundTotal function and added LesPaul's expression to the query.
It works! Thanks alot!

I did have a problem with an error for an invalid data type. Which I found to be in the tables not in coding.

Have a great weekend.

 

[barny2006]

most state/federal tax rates will not comply with rounding. they use tables, as i described above. if you try to compare your calculations with the official rate, you will see that it doesn't match.

 

[lespaul]

The State of NM doesn't have tables, it has a percentage of the sale. Currently it's 6.875% where I live in the County of Bernalillo, City of Albuquerque.

(

http://www.state.nm.us/tax/pubs/gr_rates_jul_06.pdf)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[barny2006]

it's called a percentage, like 6%. but for the break-down of each dollar, there's a table. if you ask the state tax office, they will tell you. for each so many cents within the dollar, there's a fixed amount of tax. at least that's how it's in west virginia, even though, the rate is 6%.