Rounding Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StuJol - 28 Mar 2007 00:54 GMT

I need to be able to round values to this specification using vba in Access2003

As the law requires that £0.005 or less is disregarded, we only look at the
third
decimal place in calculating NICs due where such calculation results in more
than
two decimal places.

If it is 5 or less round down.
If it is 6 or more round up.

For example, if the NIC calculated was to result in:
£67.5558 the NIC payable would be £67.55, but if the NIC payable came to
£67.5568 the NIC payable would be £67.56.

ive been using the round function but it rounds up at 5 and down at 4 but i
need it to round up at 6 and down at 5.

Can anyone help please??

Reply to this Message

Joseph Meehan - 28 Mar 2007 01:10 GMT

> I need to be able to round values to this specification using vba in
> Access2003
[quoted text clipped - 16 lines]
>
> Can anyone help please??

Try: http://ewbi.blogs.com/develops/2003/11/vba_round_yet_a.html

Signature

Joseph Meehan

Dia 's Muire duit

Reply to this Message

Jamie Collins - 28 Mar 2007 13:25 GMT

On Mar 28, 1:10 am, "Joseph Meehan" <sligoNoSPAM...@hotmail.com>
wrote:

> > As the law requires that £0.005 or less is disregarded, we only look
> > at the third
[quoted text clipped - 11 lines]
> > ive been using the round function but it rounds up at 5 and down at 4
> > but i need it to round up at 6 and down at 5.

>From your sample data I assume you are using the CURRENCY data type

and that negative values are not an issue.

The basic formula for rounding +0.005 down (towards zero) is: multiply
by one hundred, add zero-point-four, truncate, then divide by one
hundred. However, if you divide a value of type CURRENCY by an integer
the result is coerced to DOUBLE. To preserve type, multiply by
CCUR(0.01) e.g. in SQL:

SELECT
CCUR(67.5558) AS NICC_1, INT((NICC_1 * 100) + CCUR(0.4)) * CCUR(0.01)
AS result_1,
CCUR(67.5568) AS NICC_2, INT((NICC_2 * 100) + CCUR(0.4)) * CCUR(0.01)
AS result_2

Jamie.

--

Reply to this Message