Code to round up to the nearest 100

Hi All

Can I put in a query, Code to round up any numbers up to the nearest 100

I.e 97 = 100
  1997= 2000
 1421 = 1500
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can do this:

-100 * Int( [Amount] / -100)

(Thanks to Allen Browne for the basics on this:

To use this in a Query, you would do something like this in the design grid:

MyNewValue: -100 * Int(YourAmount)/-100)

This would be in the Name row of the query design grid, and would produce a new column named MyNewValue.
Try (Int(thefield ) / 100) *100
Hope it helps.
peter57rConnect With a Mentor Commented:

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

F IgorDeveloperCommented:
In access the function floor() doesn't exists (it would make the job easily),
It's my way to do that:



97:   int(96/100)*100+100 = 0*100+100 = 100
1997: int(1996/100)*100+100 = 19*100+100 = 2000
1421: int(1420/100) *100+100 = 14*100+100 =1500
1400: int(1399/100) *100+100 = 13*100+100 =1400
Mike DSolution ConsultantCommented:
How about:
get modulo of original number to the number you want to round to
subtract this from number you want to round to
add result to original number

example (in php:)
$all_original_numbers = array(97, 1997, 1421);
$round_to = 100;

foreach($all_original_numbers as  $original_number){
	$mod_number = $original_number % $round_to;
	$diff = $round_to - $mod_number;
	$rounded_nr = $original_number + $diff;
	echo "original nr: " . $original_number . " - rounded to next " . $round_to . ": " . $rounded_nr . "</br>";

Open in new window


edit: ups, just realized the topic was Access... but theory is still sound ;)
NikolasGConnect With a Mentor Commented:
I ve misplaced the / symbol in the previous post.
Try (Int(thefield / 100) *100)
Hope it helps.
DatabaseDekAuthor Commented:
Thank you all

I used


Dividing in Access always worries me. / by 0 can give an error so I always multiply. Although in the query grid it didn't seem to make any difference.

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

All Courses

From novice to tech pro — start learning today.