Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    14

    Unhappy Unanswered: Please Help! Floor() in access sql

    Would someone please tell me how to round numbers in access? I am trying to round down, and have tried floor(num,0), floor(num,1), floor(num) and all sorts of variations but can't make anything work. Rounddown also doesn't work.
    I am working on a report, but I need to tell peoples age in the report as follows:
    datediff('m', expr1, now())/12
    Therefore, I need to round these numbers down. I have checked the msowcf.dll and it is installed(i registered it at command line also)
    Thank you very much in advance!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    floor is an mssql function. Is this an adp or mdb?

    I got bored and wrote a floor function for access.
    Code:
    Public Function floor(dblIn As Double, dec As Integer) As Double
    
    decPosition = InStr(Str(dblIn), ".")
    x = Left(dblIn, decPosition + dec - 1)
    
    floor = x
    
    End Function
    Usage:

    floor(number, spaces after decimal you want rounded down)
    Last edited by Teddy; 09-22-04 at 16:07.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Posts
    14
    Teddy,

    How do I make use of the floor function example? Create a module or what?

    Isn't there a built in function in ms access to round numbers in a sql query? Floor() is used in Oracle, but it won't work in Access 2000.

    Thanks for your reply, and if you have time please help again :O)

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Access users JET-SQL. You're going to find a lot more differences then just that moving from Oracle.

    Pop that code into a module and you will be able to call it from a query the way you're used to.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I'm not sure but Int() function should also do the job?
    ghozy.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Provided your rounding to the nearest whole number sure. Not so good for rounding to the nearest 1/10 or 1/100
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you are definately right.
    now I'm thinking, to do that we could also do;
    Code:
     
    Public Function Floor(ANumber As Variant, Optional ADec As Variant= 0) As Variant
    'Default Usage : Floor(NumberToFloor)
    'Optional Usage: Floor(NumberToFloor, RoundingDecimal)
     
    	If IsNull(ANumber) Then Floor = Null: Exit Function
    	ADec = 10 ^ ADec
    	Floor = Int(ANumber / ADec) * ADec
     
    End Function
    I editted the code now it handles 1/10, 1/100, etc
    Usage is:
    floor(123424.456,1)
    floor(123424.456,2)
    floor(123424.456,-1)
    floor(123424.456,-3)
    Last edited by ghozy; 09-22-04 at 16:47. Reason: code correction
    ghozy.

  8. #8
    Join Date
    Jun 2004
    Posts
    14

    Talking Thanks for the help!

    Thank you guys! The int() function did the job! I'll work on creating that module and try it as well. I hope I can return the favor!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •