Solved

Code to round up to the nearest 100

Posted on 2012-03-15
7
524 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:DatabaseDek
7 Comments
 
LVL 6

Expert Comment

by:NikolasG
ID: 37724131
Hi
Try (Int(thefield ) / 100) *100
Hope it helps.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 200 total points
ID: 37724132
Use..

round((50+Field)/100,0)*100
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 37724138
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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 13

Expert Comment

by:F Igor
ID: 37724155
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
0
 
LVL 6

Expert Comment

by:Mike D
ID: 37724194
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


JD

edit: ups, just realized the topic was Access... but theory is still sound ;)
0
 
LVL 6

Assisted Solution

by:NikolasG
NikolasG earned 100 total points
ID: 37724226
Hi
I ve misplaced the / symbol in the previous post.
Try (Int(thefield / 100) *100)
Hope it helps.
0
 

Author Closing Comment

by:DatabaseDek
ID: 37724565
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now