Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ceiling Function (2000)

    I am an Access novice, and need some help. I am creating a database for a booking service, and have run into a problem with one of their requirements.

    When they quote prices for performances, they will only quote in multiples of $5.00. I have made this work in Excel using the CEILING function, but how do I make this work in Access? I have looked, and the ceiling function is not present in Access 2K and when I tried to use it anyway, it gave me a #Name? Error.

    Anyone have any ideas?

    If this is going to involve VBA, I am not familiar with using code or modules. I am willing to give anything a try, but am not familiar with code or module programming.


    Thanks
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ceiling Function (2000)

    Hi Greg

    I have created a function below that you can cut and paste and then use in a query.

    What you have to do is
    1. Create a new module in your access database. To do this, in the database window, click the 'modules' button and then click the 'new' button
    2. Copy the blue text below and paste it into this new module
    3. Save your newly created module (call it something like modCustomFunctions)
    4. Use the function AccessCeiling(value, significance) in a query where value is the value that you want to round and significance is the significane (in your case 5)

    <font color=6495ed><font face="Georgia">Public Function AccessCeiling(dblValue As Double, lngSignificance As Long) As Long
    '================================================= =========='
    ' Author: Jayden MacRae '
    ' Created: 29/10/2001 '
    ' Purpose: To simulate the 'Ceiling' function available in Excel which '
    ' rounds a number up to the next value of significance. '
    '================================================= =========='
    Dim lngCalculation As Long
    If dblValue = 0 Then
    AccessCeiling = 0
    Exit Function
    End If

    If dblValue / CInt(dblValue) <> 0 And dblValue / CInt(dblValue) <> 1 Then
    If CInt(dblValue) > dblValue Then
    lngCalculation = CInt(dblValue) - 1
    Else
    lngCalculation = CInt(dblValue) + 1
    End If
    Else
    lngCalculation = dblValue
    End If

    If lngCalculation < lngSignificance Then
    AccessCeiling = lngSignificance
    Else
    If lngCalculation Mod lngSignificance = 0 Then
    AccessCeiling = lngCalculation
    Else
    AccessCeiling = lngCalculation + (lngSignificance - (lngCalculation Mod lngSignificance))
    End If
    End If
    End Function</font color=6495ed></font face=georgia>

    Edited to eliminate horizontal scrolling--Charlotte

Posting Permissions

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