Ceiling function in access query

[senthil4hcl]

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]

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

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]

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

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]

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

 

[pretygirl]

I was registered at your forum. I have printed the test message. Do not delete, please.

gratuit pour mobile sonnerie portable mp3 - Sonnerie portable MP3 est en effet un pouvoir de l'innovation technologique. Êtes-vous familier avec cette sonnerie portable?gratuit pour mobile sonnerie portable mp3

 

[sparks80]

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]

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:

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]

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]

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

 

[gemma-the-husky]

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]

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]

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

 

[YS2020]

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,