round up the result int to next whole numberr

Question

Post reply

round up the result int to next whole numberr

Frances L

SSCarpal Tunnel

Points: 4727
More actions
October 11, 2006 at 9:37 am

#68447

I remeber there is function like this
11267/500=22.534 but I want the results show as 23.
If the result is bigger than the whole number, it will go into the resulted whole number +1
Do you think I need to write my own function ? Thx.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

Try this

DECLARE @a INT

DECLARE @b-2 INT

SELECT @A = 11267

SELECT @B = 500

SELECT CASE @a%@B WHEN 0 THEN @a/@B ELSE (@A/@B)+1 END

Carl Carter-348574 Mr or Mrs. 500 Points: 571 More actions · Answer 2

This functionality is built into Excel but not Access (which is where I assume you wish to use this). However, using the magic of references we can still use everything from Excel in Access by doing the following:

Open the VBE (Alt+F11) and go to Tools, References. In there tick Microsoft Excel 10.0 Object Library - the number may differ depending on what version of Office you're running (I'm running 2002) - then click OK.

Paste this function below into a module.

Function udfRoundUp(ByVal dblNumber As Double)

udfRoundUp = Excel.WorksheetFunction.RoundUp(dblNumber, 0)

End Function

and you can then use that function anywhere within that database.

To call it from within a query just do FieldName: fRound([fieldyouwanttoroundup]) or from within VBA the usual way.

The Excel.WorksheetFunction is a very handy namespace for calculations. There is also RoundDown available etc.

Carl Carter-348574 Mr or Mrs. 500 Points: 571 More actions · Answer 3

Just spotted a typo:

To call it from within a query just do FieldName: fRound([fieldyouwanttoroundup]) or from within VBA the usual way.

should've been:

To call it from within a query just do FieldName: udfRoundUp([fieldyouwanttoroundup]) or from within VBA the usual way.

Cheers

Carl

Lisa Slater Nicholls Hall of Fame Points: 3439 More actions · Answer 4

I think there might be an easier way than adding references to Excel ...

Try something like this:

x = 37.234

? ABS(INT(x * -1))

(Note: I don't use Access, but couldn't believe this wasn't possible, so I checked the help file. The clue that you could do it this way came from the fact that Access actually has a FIX() function for negative numbers, which works differently from INT()).

>L<

Carl Carter-348574 Mr or Mrs. 500 Points: 571 More actions · Answer 5

Hi,

Good suggestion however it'll round up anything using that method.. ie:

? ABS(INT(2.443 * -1))

3

Carl Carter-348574 Mr or Mrs. 500 Points: 571 More actions · Answer 6

Ok - my bad - just noticed that my example using RoundUp function also rounds up anything

so in the function change it like the following and it will round up / down as required.

Function fRound(ByVal dblNumber As Double)

    fRound = Excel.WorksheetFunction.Round(dblNumber, 0)

End Function

Cheers

Carl

Lisa Slater Nicholls Hall of Fame Points: 3439 More actions · Answer 7

"However"?

I think that is exactly what the poster wanted??

>L<

Carl Carter-348574 Mr or Mrs. 500 Points: 571 More actions · Answer 8

oooh my bad . You're right I obviously didn't read it closely enough

angusj4 SSC Rookie Points: 25 More actions · Answer 9

My formula is: =nper(.07/12,-300,15000)

Exactly how do I round that up?

angusj4 SSC Rookie Points: 25 More actions · Answer 10

My formula is: =nper(.07/12,-300,15000)

Exactly how do I round that up?

The result comes out to 59.287765, to round that up to 60 what would my formula look like? Please one at a time I'm a newbie! Make it as simple as possible for me to understand.

angusj4 SSC Rookie Points: 25 More actions · Answer 11

angusj4

SSC Rookie

Points: 25

October 17, 2008 at 2:27 pm

#886665

Amount of help on a scale from 1-10:ZERO!

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 12

Are you trying to do this in VBA in Access or SQL in Access?

Using SQL, there is a function called CEILING.

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

angusj4 (10/17/2008)
Amount of help on a scale from 1-10:ZERO!

I must say I have to congratulate you on pretty much ensuring you will not get any help. eight minutes posted and back with attitude which is essentially guaranteed to get you ignored.

Bravo!

Or - as you might say: TEN

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?