# 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?

x
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:
Hi
Try (Int(thefield ) / 100) *100
Hope it helps.
Commented:
Use..

round((50+Field)/100,0)*100
Infotrakker SoftwareCommented:
You can do this:

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

(Thanks to Allen Browne for the basics on this: http://allenbrowne.com/round.html)

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.

Experts Exchange Solution brought to you by

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

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

int(([value]-1)/100)*100+100

I.e:

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
Solution ConsultantCommented:
get modulo of original number to the number you want to round to
subtract this from number you want to round to

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>";
}
?>
``````

JD

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

I used

-100*Int([Value]*-0.01)

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