#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2006
    Posts
    2
    Rep Power
    0

    ROUNDUP function in MS Access query


    Hi,
    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.
    Expr1: ROUNDUP([compass_pro_cost_data]![billed_weight],0)
    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 ?
  2. #2
  3. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2006
    Location
    USA
    Posts
    21
    Rep Power
    0
    Hello:
    That means that there is no function entitled RoundUp. The function you want to use is Round
    '
    Syntax
    Round(expression [,numdecimalplaces])
    '
    Regards
    Mark
  4. #3
  5. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2462
    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
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2006
    Posts
    2
    Rep Power
    0

    ROUND (UP) in Access


    Originally Posted by mehere
    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.
    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
    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.
  8. #5
  9. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2462
    try this:
    Code:
    ROUND([compass_pro_cost_data]![billed_weight]+0.5,0)
    or you can create a public function to do this and use that in your expression"
    Code:
    Public Function RoundUP(RoundMe As Double)
    
    Dim RoundAnswer As Double
    
    RoundAnswer = Round(RoundMe, 0)
    
    If RoundAnswer < RoundMe Then
    RoundAnswer = RoundAnswer + 1
    End If
    
    RoundUP = RoundAnswer
    
    End Function
    then your expression:
    RoundUp([compass_pro_cost_data]![billed_weight])
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2011
    Posts
    1
    Rep Power
    0

    Another solution


    Originally Posted by mehere
    try this:
    ...you can create a public function to do this and use that in your expression
    Thanks Mehere. You helped me think of a slightly different solution (basically the same as yours but without needing to create a function).

    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.
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Jan 2008
    Posts
    1,237
    Rep Power
    237
    there's an easier way I think

    Let's say your field is DecPrice

    RoundedUp: IIf([decprice]-Int([decprice])<>0,Int([decprice])+1,Int([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!

Similar Threads

  1. Add COUNT function to query
    By nmajdan in forum SQL Development
    Replies: 3
    Last Post: June 5th, 2006, 03:05 PM
  2. ASP --> Access Parameter Query
    By irishpaddy in forum ASP Development
    Replies: 6
    Last Post: May 23rd, 2006, 11:12 AM
  3. Access Query + ASP again :)
    By Tulkas in forum ASP Development
    Replies: 8
    Last Post: April 27th, 2006, 01:34 PM
  4. Run an Access Query in VB
    By leonepaolo in forum Visual Basic Programming
    Replies: 1
    Last Post: March 11th, 2005, 06:51 AM
  5. Generate a function through Access
    By plethoraofvalha in forum Microsoft Access Help
    Replies: 4
    Last Post: August 26th, 2004, 09:56 AM

IMN logo majestic logo threadwatch logo seochat tools logo