The Round function, like implicit rounding when numbers are
displayed on a form/report, does something called Bankers
Rounding. That's where .5 is rounded to the nearest even
number, e.g. 3.5 rounds to 4 and 4.5 also rounds to 4.
You say you want to "round up", but there are many ways that
can be defined. Most common is always taking .005 up to
..01. If that's what your government wants (mine sure
doesn't), then I think this function can be used as a
skeletal outline:
Public TaxRound(num)
TaxRound = Fix(num * 100 + Sgn(num) * .5) / 100
End Function
You'll have to determin if using Variant argument and result
is appropriate in your situation. Maybe it should be Double
or Currency?? You may also need to add some code for
argument range and/or error handling
You still need to verify that algorithm against tax
regulations. After all, there is a liability to doing it
wrong. OTOH, most tax agancies allow you to make a rounding
adjustment to cover this kind of situation. You should at
least consult with your accountant to see what s/he thinks
the rules are.
--
Marsh
MVP [MS Access]
That was helpful - The only thing is that I need it to round up and not down.
Is that easily accomplished?
Marshall Barton said:
I don't think I would do this in the query, but it should
work if you think that's appropriate.
You could try to use the built-in Round function
=Round(SumTerm1 * TaxRate, 2)
However, I seriously doubt that the Round function uses the
same rounding rules that your government tax agency uses.
It is important for you to determine the rules they use and
then program the rules into a function. We can help you
with the function's coding, but only after you explain the
rules you must follow.
I'm sorry -You lost me when you said Invoice field - You're right - I am
using a simple expression of :[SumTerm1]*[TaxRate] in the report to get my
tax amount. Are you saying I have to figure a way of doing this in a query
(ugh!) and then put that into my report? Ideally, if I can get it to round
up at two decimals, I think I'd be fine.
By way of background, I'm a self-taught Access user so I'm not familiar with
all of the technical jargon.
Thanks
:
It sounds like your tax calculation is not quite correct.
You are probably just using an expression like
=InvTotal * TaxRate
which does not include the government's rules about
rounding. I don't know what those rules are, but maybe you
should create a function to preform the more complete
calculation, then call the function to determine the tax
amount and store that in the invoice field.
Beetlejuice wrote:
Okay! that was helpful - thank you- now I know where my problem is - It's my
tax calculation. How do I get the tax to round up to two decimals (the feds
need my pennies!) and stay as a fixed number so it doesn't mess up the rest
of my calculation?
:
Try display 4-5 decimal places on all your currency fields. See what you get
for results.
--
I'm not sure about the rounding issue. This is an invoice for a dance
studio
where there are three term payments and a costume payment.
Set out as follows:
In the details section I have:
Term 1 Term 2 Term3 Costume
$727.53 $707.23 $657.23 $0.00
It's giving me a total of $2,087.98 - which should be $2,087.99
Then in the footer section
I have the payment information which is
Cheque ($1,430.76)
Cheque ($657.23)
And it gives me a correct total of ($2,087.99)
and then an incorrectly calculated balance of ($0.02)
Any more thoughts on this? It's a small thing but extremely aggravating .
. .
Beetlejuice wrote:
I have an invoice set up as a report. The details section has three
different Sums but when I tried to add the three Sums together, it's a
penny
off (one penny less) - Why and how do I fix?
:
Probably a common rounding issue. Most people forget that
the sum of rounded values is NOT the same as rounding the
sum of the values. The total of the three values is more
accurate than your mental addition of the three values. For
example: Let's say you have three values 1.2 3.6 and 4.6.
Those will be displayed as 1, 4 and 5, which you mentally
add to 10. However, the real total is 9.4 which is
displayed as 9.
Unfortunately, most people can not deal with this and jump
to the conclusion that the total is wrong. To deal with
this psychological issue, you can just add the rounded
values that are displayed in the individual values. E.g.
the (wrong) total can be calculated using an expression
like:
=Round(firstsum,2) + Round(sum2, 2) + Round(sum3, 2)
