How can I round to the nearest 5 cents

I am trying to round to the nearest nickel using the expression builder in Access.  What should this expression look like.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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 Commented:
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
Commented:
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 Commented:
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
Commented:
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

Experts Exchange Solution brought to you by