Rounding Values

[Guest]

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??

 

[Joseph Meehan]

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??

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

 

[Jamie Collins]

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.

--