Solved

# How can I round to the nearest 5 cents

Posted on 1999-11-05
Medium Priority
1,689 Views
I am trying to round to the nearest nickel using the expression builder in Access.  What should this expression look like.
0
Question by:Corny
• 2
• 2

LVL 10

Expert Comment

ID: 2186589
What about something like (I have no idea what the rest of your expression looks like, so this is a guess):

iif(right(YourField, 1) < 3 or right(YourField, 1) > 7, 0, 5)

You might be better off putting this thing into a function, though, and calling that from your query/form/whatever. Something like:

Public Function NickelRound (varValue as Variant) as Currency

Select Case right(varValue, 1)
Case < 3, > 7
NickelRound = left(varValue, len(varValue) -1) & 0
Case else
NickelRound = left(varValue, len(varValue) -1) & 5
End Select

End Function

Hope that helps . . .

brewdog
0

Author Comment

ID: 2186648
Ok but this will not round \$2.95 to \$3.00 - at least I don't think so.

I'm playing with
=IIf(Right([Text279],1)<3 Or Right([Text279],1)>7,0,5)

Where in my form' Text279 already has a very long and complicated formula.
0

LVL 10

Expert Comment

ID: 2186657
wait a minute. You want to pass in \$2.95 and have it return \$3.00? That wouldn't be rounding to the nearest nickel. :o)

If you're using this on a form, I'd use the function. Then if you want to modify it at any time (like making exceptions for .95 cents) you can do it much easier than altering the IIF. Set your text box's ControlSource to

=NickelRound(Text279)
0

Author Comment

ID: 2187215
Sorry  - I meant \$2.97 needs to round up to \$3.00.
I guess I need to figure out how to write functions instead or using the expression builder. I'll work on that.

I had to go into work to fix a problem, otherwise I would have got back to you sooner...Corny
0

Accepted Solution

hlominac earned 200 total points
ID: 2187990
Here is a function which will do the rounding to nearest 5 cents.

Public Function RndNickel(Amount As _ Currency) As Currency
Dim Temp As Currency
Temp = Fix(Amount * 20 + 0.5)
RndNickel = CCur(Temp / 20)
End Function

This function works for positive numbers only!  If you wish to round negative numbers, check for negative, change to positive and return negative result if test was negative.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.