?
Solved

Round UP to next integer in MS Access 2000 in a query?

Posted on 2003-02-26
23
Medium Priority
?
540 Views
Last Modified: 2008-01-09
What's best way to Round UP to next integer in MS Access 2000 in a query?
0
Comment
Question by:KButler6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 4
  • +4
23 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8029258
Generally, I would use a function like this:
IIf(Fix([MyField])<>[MyField],Fix([MyField]+1),Fix([MyField]))
0
 

Expert Comment

by:pmeloni
ID: 8029366
You can use the function INT(yournumber) that made exactly for that.

Hope that help
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8029393
Int only chops off the decimal part of the number - it doesn't perform any rounding.
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Expert Comment

by:guggs
ID: 8029577
From Access Help file:

ROUND
Rounds a number to a specified number of digits.

Syntax

ROUND(number,num_digits)

Number   is the number you want to round.

Num_digits   is the number of digits you want to round to. Negative rounds to the left of the decimal point; 0 (zero) rounds to the nearest integer.

==============================
If that doesn't work try adding .5 to the number then use the INT function: INT(number + .5)

This is an old programming trick :)
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8029624
=Round([YourField],0)

and nice trick, guggs!
0
 

Expert Comment

by:guggs
ID: 8029663
Sorry not INT, but ROUND function:

SELECT ROUND( <value> +.5, 0) AS Number

where <value> is the value or variable you want to round
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8029835
Two points about using the Round function:

First of all, it's not supported in Access 97 - I don't know if it is in A2K, but it's definitely in A2K2.

Secondly, like CLng, it rounds to the nearest EVEN number. Therefore, Round(1.5,0) and Round(2.5,0) both return 2. You cannot simply add 0.5 to the number to round. Consider what happens if <value> is exactly 1:
Round(1+0.5,0) = 2
Therefore you must apply a check to see if the value is already an integer amount before you perform the rounding.
0
 

Expert Comment

by:guggs
ID: 8029998
True.. a few tests shows:
1) ROUND does work in Access 2000
2) The function operates correctly by itself... no need the +.5
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8032168
That's true, but the questioner was asking to always round up - I assumed this meant that if the number had any decimal part at all, it was to be rounded up to the next integer (as opposed to mathematical rounding). This isn't uncommon in situations like pricing and so on.
0
 
LVL 1

Expert Comment

by:EmilG
ID: 8034985
Here is a function that rounds up a number so that 234.34 would become 235.00. It also preserves if the number is positive or negative. If that is what you are trying to do you can use the following.

Good Luck

Option Compare Database
Option Explicit

Function RoundUpMod(ByVal Number As Currency) As Currency

    Dim P As Integer
    Dim N As Currency

    If Number < 0 Then P = -1 Else P = 1
    N = Abs(Number)
    N = N * 100
    N = Int(N)
    While Right(N, 2) <> 0
    N = N + 1
    Wend
    N = N / 100
    N = N * P
    RoundUpMod = N

End Function
0
 

Expert Comment

by:guggs
ID: 8036677
Okay sorry for all the bad answers.  Here's your answer tested and working:

SELECT CLng( number + .5) as Expr;

This statement works in Access 2000 and will round up any number to the next whole number.  For example:
2.00001 -> 3
2.9 -> 3
2.0 -> 2

-guggs
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8036758
Umm, but 1.0 will still round to 2.
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8060165
are you sure? works for me...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8060821
Yep, pretty certain. In the immediate window, type:
?CLng(1.5)

1.5 being 1+0.5. You should get 2.
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8060863
then why does 2.0 ...

2.0 + .5 = 2.5

round to 2? or is guggs wrong in his post on 2/27/03 12:36PM
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8060916
Nope, guggs is correct, but when the decimal part is exactly 0.5, CLng rounds to the nearest even number, ie:
CLng(3.5)=4
CLng(4.5)=4
CLng(5.5)=6
CLng(6.5)=6
0
 
LVL 18

Expert Comment

by:1William
ID: 8771969
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Delete question, no refund
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
1William
EE Cleanup Volunteer
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8771982
I think my original answer would have worked :)
0
 
LVL 1

Expert Comment

by:EmilG
ID: 8814100
If there are only 2 decimal places my answer would work as well. If the number of decimal places vary then you would have to test for the number of decimal places and change the multiplier/divisor programatically for each number. :)

Option Compare Database
Option Explicit

Function RoundUpMod(ByVal Number As Currency) As Currency

   Dim P As Integer
   Dim N As Currency

   If Number < 0 Then P = -1 Else P = 1
   N = Abs(Number)
   N = N * 100
   N = Int(N)
   While Right(N, 2) <> 0
   N = N + 1
   Wend
   N = N / 100
   N = N * P
   RoundUpMod = N

End Function
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8820630
PAQ NO Refund (200 pts)

The question is too ambiguous to determine if shane's function will work.
If the intention is that any fraction rounds up (to include say .1) then Shane's function should work.  If the intention is past .5 it will round up more frequently half the time.

Also, negative numbers I believe are a challenge with shane's code as well.

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8821191
Bleah :P Agreed though, the original question needed more elaboration and despite all the comments, there was no feedback from the questioner.
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8822054
Shane,

As you may know, PAQ/No Refund is the WORST option: If the question has enough information to be called an answer, then someone's comments should be selected; if not, then the question should be deleted.

But in this case, it actually fits well. The questioner never replies and there was a decent amount of debate as to what would work and what wouldn't. It may have some good information for the PAQ, but not enough to award.

However:

>>> >>> I think my original answer would have worked :)
that sounds as if you never tested your original answer... if you could prove that your solution works, then that's a different story... ;-)

Andrew
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8822173
Nex,

I am aware that PAQ/No Refund is one of the least favoured options.

As to my answer not being tested - it *was* tested. It is a formula I use in a number of applications, and it works PROVIDED that the requirement is to round a positive number UP to the next integer regardless of the decimal part.

There was not sufficient indication that this was indeed the questioner's requirement, hence my comment that I agree with Spidey's decision.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

801 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