Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-02-26
23
Medium Priority
?
752 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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