Ceiling Function (2000)

Topic

New Reply

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

Reply | Quote

Replies

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)

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

Edited to eliminate horizontal scrolling–Charlotte

Reply | Quote