Solved

RoundUp in Access VBA

Posted on 2013-12-19
9
556 Views
Last Modified: 2013-12-19
I am trying to round a number (double) up to the next integer.  Here is a sample of the results I am looking for:

 1.25 rounds to 2
 .333 rounds to 1
 1.8 rounds to 2
1.111 rounds to 2

and so forth.  Anyone have a function to do this?
0
Comment
Question by:marku24
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 39730549
Try:

Int([Your Number]+0.99999)

Open in new window

0
 

Author Closing Comment

by:marku24
ID: 39730567
that is just ridiculously simple, but it does work.

thanks
0
 
LVL 75
ID: 39730605
Of course, this fails:

int(1.000001 +.999999)
 = 1
as well as other cases.

This never fails:

-Int(-[YourNumber)

-int(-1.000001)
= 2

Thanks for Gustav (cactus_data) for this trick.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 21
ID: 39730648
Great trick to round down a negative.

This also works. You must force the data type for teh math.

? int(CDbl(1.000001 +.999999))
2
0
 

Author Comment

by:marku24
ID: 39730661
thank you very much.
0
 
LVL 75
ID: 39730702
Fails:
int(CDbl(1.0000000001 +.999999))
 =1

:marku24:
if you want accuracy, use this to Round up:
-Int(-[YourNumber])

Round down:

Int(YourNumber)

mx
0
 
LVL 21
ID: 39730776
I agree that -Int(-[YourNumber]) eliminates the chances for errors in accuracy. It should be the preferred method.

And it works for negative numbers!
? -int(-(-1.1))
-1 

Open in new window

0
 

Author Comment

by:marku24
ID: 39730782
will do.  I am looking for rounding up only and will adjust accordingly.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39731160
Thanks Joe!

I should add that I can't take the credit, I'm rather the messenger. The method is as old as BASIC itself.

/gustav
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

733 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