# Ceiling function in access query (1 Viewer)

#### senthil4hcl

##### New member
Hi All

I m using access query
where i need something similar to excel ceiling function

For an eg.,

In excel if the value is 213 then ceiling(213,5) =215
216 ceiling(216,5)=220

So i want to round off the value in field to 5

select Ceil(216,5)

output should be 220

Pls help me

#### DCrake

##### Remembered
You could actually write your own Ceiling function and use that. Something along the lines of

Code:
``````Public Function Ceiling(ByVal AnyValue,Roof As Integer) As Integer

Dim sStr As String
sStr = CStr(AnyValue)

If Roof = 5 Then
If Right(sStr,1) = "0" Or Right(sStr,1) = "5" Then
Ceiling = AnyValue
Exit Function
End If
End If

Select CaseVal(Right(sStr,1))
Case 1, 2, 3, 4
Ceiling = Val(Left(sStr,Len(sStr)-1) & "5")
Case 6, 7, 8, 9
Ceiling = Val(Left(sStr,Len(sStr)-1) & "0")
End Select

End Function``````

What I have not trapped in the function is single digit values
Above is aircode and has not been tested.

CodeMaster:

#### Rabbie

##### Super Moderator
Hi All

I m using access query
where i need something similar to excel ceiling function

For an eg.,

In excel if the value is 213 then ceiling(213,5) =215
216 ceiling(216,5)=220

So i want to round off the value in field to 5

select Ceil(216,5)

output should be 220

Pls help me
Try putting this function into a VBA module

Code:
``````Function Ceiling(Number As Double, Significance As Double) As Double
If Number / Significance - CDbl(CLng(Number / Significance)) = 0 Then
Ceiling = Number
Else
Ceiling = Significance * (CLng((Number + Significance / 2) / Significance))
End If
End Function``````

Make sure the module isn't called the same as the function

#### senthil4hcl

##### New member
Solved the problem

Actually i need excute a ceiling function in access query

I used this small calculation in select statement

Eg:
select (-Int(-441 / 5) * 5) = 445

or
select 423-(423 mod 5)+5 =425

#### sparks80

##### Physicist
A late reply to this for anyone needing a function that will work directly in a query.

senthil4hcl's solution will round up the answer, even if the number is divisible with no remainder.

This should handle all cases:
Ceiling: IIF(A mod B = 0, A, A + B - (A mod B))

e.g. A=214, B=2, Ceiling = 214
A=215, B = 2, Ceiling = 216

#### jdraw

##### Super Moderator
Staff member
I found this on another forum

I learned some time ago that there is no need to recreate the wheel to use Excel functions. All you have to do is set a reference to Excel and the functions are available.
It is recommended that you wrap them in an Access function wrapper (but that is only required if you wish to be able to use them in queries... from inside VBA they are immediately available).
In this case, to use the Excel function in VBA, simply set the reference and then call it as follows:
For instance, after executing the following line, myVal would = 2
myVal = Excel.worksheetfunction.Ceiling(1.287, 1)
To place it in a wrapper, use the following:
Code:
``````Public Function fCeiling(vDblValue As Var, vSignificance As Var) As Double
fCeiling = Excel.WorksheetFunction(vDblValue, vSignificance)
End Function``````
-credit to the author
Glenn J. Koster, Sr.
Eaton Hydraulics
Hutchinson, KS

#### sparks80

##### Physicist
Hi,

It is possible to write custom VBA functions or wrappers for Excel functions as shown above. However this is much slower to execute than using the built-in query functions. If there are only a small number of records, or speed is not an issue, then any of the approaches above will be fine.

#### smig

##### Registered User.
I think if it's only require few lines of code to rewrite the function it will be a better solution.

#### gemma-the-husky

##### Super Moderator
Staff member
another issue with using the excel library, is that you run the risk of having reference problems when you move the database from machine to machine.

I can think of little that can be accomplished in excel, that cannot be easily accomplished in access

#### YS2020

##### New member
Hi All

I m using access query
where i need something similar to excel ceiling function

For an eg.,

In excel if the value is 213 then ceiling(213,5) =215
216 ceiling(216,5)=220

So i want to round off the value in field to 5

select Ceil(216,5)

output should be 220

Pls help me

use SQL like this SELECT PRICE, ((PRICE\500)+1)*500 as CEILING FROM STOCK WHERE CODE= "S00001252"
similar result with excel function CEILING(number,significance) -- > Ceiling(Price,500).

#### Gasman

##### Enthusiastic Amateur
We are talking about a 12 year old thread here, with last post 8 years ago?

#### YS2020

##### New member
CEILING(216,5) = 220
explanation :
same with 216/5 =43.2, if you use 216 Div 5 then result will be 43. so after div you add by 1. with mean you get result 44. then 44 * 5 = will get result 220,

Replies
15
Views
198
Replies
5
Views
201
Replies
2
Views
129
Replies
5
Views
432
Replies
15
Views
192