round numbers so the total is divisible by 5

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI
I have the round function set with 2 decimal places on a currenty field in
an Access table. This field calculates quantity x cost. The cost of an item
could be below $1.00. The quantity could be a whole number (1) or a part of
a whole (.5, 1.3). I need to have the calculation round up to the nearest
number divisible by 5. Ex: 1 x $ .75 should equal $ .75, but my total
calculates as $ .80. If the number is already divisible by 5, I don't want
the number to round up. Other examples are: 1.2 x $ .20 ($ .24) should round
to $ .25 OR 1.7 x $ .20 ($ .34) should round to $ .35 OR 1.3 x $ .20 ($ .26)
should round to $ .30.
I don't know anything about writing "code". I do my designing for
calculations only using queries, controls on forms or reports, and macros. I
have played a tiny bit with the modules, but I really don't know what I am
doing in that area.
Can anyone advise me on this rounding issue? Thanks so much.
 
CInt(Qty * Price * 20) / 20

should do what you want. For example (from the Debug window):

?CInt(1.2 * 0.2 * 20) / 20
0.25
 
The quantity could be a whole number (1) or a part of
a whole (.5, 1.3). I need to have the calculation round up to the
nearest number divisible by 5.
Click to expand...

Iif(5*Int(Amount/5)=Amount, Amount, 5+5*Int(Amount/5))

You can put this is the controlsource of a control on a form or report, or
in the calculation part of the query grid (unless you are working in adp &
SQL server, probably).

If you want to change the rounding to 5 cents (rather than 5 dollars),
which seems to be sense of your original post, then change all the fives to
0.05


Hope that helps


Tim F
 
Back
Top