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.
CornyAsked:
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.

brewdogCommented:
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
CornyAuthor 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
brewdogCommented:
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
CornyAuthor 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
hlominacCommented:
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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.