How can I round to the nearest 5 cents

Posted on 1999-11-05
Medium Priority
Last Modified: 2010-05-18
I am trying to round to the nearest nickel using the expression builder in Access.  What should this expression look like.
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 . . .


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


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

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.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question