August 8th, 2006, 07:09 AM
ROUNDUP function in MS Access query
I am on a elementary level of MS Access skill.
I am trying to create an expression, where I want to round up the value in a field.
When executing the qry, I get :
"Undefined function "ROUNDUP" in expression, I am using Access 2000. What am I doing wrong, or Is there another way ?
August 8th, 2006, 09:15 AM
That means that there is no function entitled RoundUp. The function you want to use is Round
August 8th, 2006, 09:59 AM
however, if what you really want to do is ROUND UP all values, then you will need to create a user function for it. ROUND will round depending on the decimal value. it it's 1.21 it will round to 1, if it's 1.52 it will round to 2.
Quote of the Month:
Planning: Much work remains to be done before we can announce our total failure to make any progress.
Questions to Ponder:
If convenience stores are open 24 hours a day, 365 days a year, why are there locks on the doors?
iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright© 2008 sbenj69
August 8th, 2006, 10:34 AM
ROUND (UP) in Access
Hi there, Thank you for the responses, I need to round up to next full number, eg. 0.1 to 1. and 0.5 to 1
Originally Posted by mehere
Previouseley, I exported the result table to Excell, inserted ROUNDUP formuale, and imported back to Acess, too long winded.
I am afraid I do not know how to create user function.
August 8th, 2006, 10:55 AM
or you can create a public function to do this and use that in your expression"
then your expression:
Public Function RoundUP(RoundMe As Double)
Dim RoundAnswer As Double
RoundAnswer = Round(RoundMe, 0)
If RoundAnswer < RoundMe Then
RoundAnswer = RoundAnswer + 1
RoundUP = RoundAnswer
July 22nd, 2011, 05:33 AM
Thanks Mehere. You helped me think of a slightly different solution (basically the same as yours but without needing to create a function).
Originally Posted by mehere
I needed to round up only, in my case to the nearest £0.50.
So I created a column/field in my query called DecPrice (the decimal price accurate to lost of decimal points).
I then created a field called RoundedPrice: Round([DecPrice],0)
I then created the field I wanted to use called MyPrice: IIf([roundedprice]<[decprice],[roundedprice]+0.5,[roundedprice])
You can also use this for MyPrice if you want to round up to the nearest £1 instead of the nearest £0.50: MyPrice: IIf([roundedprice]<[decprice],[roundedprice]+1,[roundedprice])
Hope that helps someone.
July 22nd, 2011, 02:39 PM
there's an easier way I think
Let's say your field is DecPrice
The INT function just takes the integer value of a field and ignores any decimal places
If we've helped you and you have solved your problem please post that it's been resolved so we know! The suspense kills me!