roundup

[RussG]

Hi - I have done a search but can't find exactly what I need.

I have an order database and the product prices need to be updated by 2% from April 1st. I have tested running an update query which works fine, I have also tested the same against futire orders dated from 1st April and the update query works a treat.
However, now I am asked to round up all the prices to the nearest 5p - which I will also need to do for the future orders.

Can anyone offer any advise on the best way to achieve this.

Thanks
RussG

 

[gemma-the-husky]

so £2.23 rounded to £2.25

these steps

1. add .0499 ie slightyl less than 5p
2.23 + .0499 = 2.2799
'this ensures than any amount is rounded up, but doesnt go to the next 5p.
so 5p itself, increases to 9.99p, which will round down back to 5p
but 5.5p increases to 10.49p, which will then round to 10p

2. multiply by 100, to change the sum into pence. (the key to this is losing the rtemainder, hence we need this in whole pence)
x 100 = 227.99

3.because we want to round to 5ps, we need to determine how many exact multiple of 5p, there are

so 227.99 divided by 5 = 45.598

4. now round this down - either int or fix function

int(45.598) = 45

5. now we can revese steps 2 and 3

so multiply by 5
x 5 = 225

6.
and divide by 100
= 2.25


----------------------
so try this

Function round5(amount As Double) As Double
round5 = (Int(((amount + 0.0499) * 100) / 5) * 5) / 100
End Function


[edited - I hadnt tried this, and there was a slight error - above function now tested and works]

 

[RussG]

Many thanks for this Dave

Really appreciate the help.

RussG