Ceiling function in access query (1 Viewer)

senthil4hcl

New member
Local time
Today, 04:44
Joined
Oct 22, 2008
Messages
2
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
Local time
Today, 11:44
Joined
Jun 8, 2005
Messages
8,632
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::cool:
 

Rabbie

Super Moderator
Local time
Today, 11:44
Joined
Jul 10, 2007
Messages
5,906
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
Local time
Today, 04:44
Joined
Oct 22, 2008
Messages
2
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
Local time
Today, 11:44
Joined
Mar 31, 2012
Messages
223
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
Local time
Today, 07:44
Joined
Jan 23, 2006
Messages
15,364
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:
myVal = Excel.worksheetfunction.Ceiling(vSomeNumber, vSignficance)
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
Local time
Today, 11:44
Joined
Mar 31, 2012
Messages
223
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.
Local time
Today, 13:44
Joined
Nov 25, 2009
Messages
2,209
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
Local time
Today, 11:44
Joined
Sep 12, 2006
Messages
15,614
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
Local time
Today, 18:44
Joined
May 2, 2020
Messages
2
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
Local time
Today, 11:44
Joined
Sep 21, 2011
Messages
14,051
We are talking about a 12 year old thread here, with last post 8 years ago? :unsure:
 

YS2020

New member
Local time
Today, 18:44
Joined
May 2, 2020
Messages
2
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,
 

Users who are viewing this thread

Top Bottom