Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RoundUp function in Access (2003)

    Is there a way to replicate the Excel RoundUp function?
    I've tried referencing the Excel 11.00 Object Library in the VBA editor Recerences window and use the following code in the module for a report that uses the function

    Private Function rates(varToTest As String, tape As Date)

    Select Case varToTest
    Case "*3"
    rates = roundup(tape, 0) * 3
    Case "*4"
    rates = roundup(tape, 0) * 4
    Case "*5"
    rates = roundup(tape, 0) * 5
    Case "*2"
    rates = roundup(tape, 0) * 2 + 5
    Case "

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RoundUp function in Access (2003)

    In order to use Excel functions in Access, you'd have to create an Excel application object, so you'd be running an invisible instance of Excel just to perform some calculations. That's a lot of overhead.

    You could copy this function into a module:

    Public Function Roundup As Variant(Number As Variant, NumDigits As Integer) As Double
    Roundup = -Sgn(Number) * Int(-Abs(Number) * 10 ^ NumDigits) * 10 ^ -NumDigits
    End Function

    If you only want to be able to round up to the nearest whole number, you can simplify it to

    Function Roundup(Number As Variant) As Long
    Roundup = -Sgn(Number) * Int(-Abs(Number))
    End Function

    Note: neither function will handle null values. If you need that, you can use (for example)

    Function Roundup(Number As Variant) As Variant
    On Error Resume Next
    Roundup = -Sgn(Number) * Int(-Abs(Number))
    End Function

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RoundUp function in Access (2003)

    Great job Hans, thank you.

Posting Permissions

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