Ceiling Function?

[straybullet]

I need to use the Excel ceiling function in an Access DB.

So far, Ive been trying to work with:

Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) _
As Double
Ceiling = (X \ Factor - (X / Factor - X \ Factor > 0)) * Factor
End Function

When running the query, this produces error 11 (division by 0) even though none of the cells are blank nor do any have a zero value. SP1: Ceiling([HPC_BASE]/[MARKUP_1],[MARKUP_2])

What SP1 (Sale Price) needs to be is the HPC_BASE (Base Hard Product Cost) divided by MARKUP_1 (value less than 1 - currently .67 or .72). The result needs to be rounded up to the value of MARKUP_2 (chosen increment for the product - .10, 10, 100)

Can anyone suggest either the proper error handling or a even a completely different function?

Let them hate - so long as they fear... Lucius Accius

 

[ZmrAbdulla]

How to use ceiling function in access

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[straybullet]

TYVM - This got rid of the error messages - however, for some reason, the increment portion isnt working consistently...

Ex:
SP1: ceiling([hpc_base]/[markup_1],[MARKUP_2])
equates to
SP1: ceiling(878/.67,10) should result in 1320, yet the result in the query is 1314.
and
SP1: ceiling(2.19/.67,0.1) should result in 3.3, yet the result in the query is 6.
but
SP1: ceiling(1323/.67,10) has a proper result of 1980.

Is there something I can change?

Let them hate - so long as they fear... Lucius Accius

 

[ZmrAbdulla]

That one is not wroking well.
here is something correct (and tested)

http://www.weascend.com/accesscode/ceiling.htm

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[straybullet]

Have also tried the following:

Function vCeiling(ByVal iOne As Double, ByVal iTwo As Double)
vCeiling = Excel.WorksheetFunction.ceiling(iOne, iTwo)
End Function

However, for some reason, Access refuses to recognize the function - declaring it as undefined.

Other than quitting my job and moving to a cave (one option being considered...) any suggestions for forcing Access to understand what I expect?

Let them hate - so long as they fear... Lucius Accius

 

[ZmrAbdulla]

it is easy if you are pasting that code into a module and use something like below in a new field in a query

NewValue: vCeiling([Field1],[Field2])

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[MichaelRed]

something poorly undestood this way comes?

. . .

for at least two participants.

Perhaps one or more would care to critiquMY understanding(s):

First:

ceiling(878/.67,10) should result in 1320, yet the result in the query is 1314.

Seems "unjustified". From the generic, rounding to 10, should not ever produce a result ending in 4?

Second:

How to use ceiling function in access

might be easier supplied with a more simplistic suggestion to to simply add a reference to the Excel LIBRARY and then to invoke the functions, as in

? Excel.WorksheetFunction.Ceiling(878/.67,10)
 1320

which also confirms that the rounding aspect of the function (in Excels' version of ceiling would not retur a value ending in 4 when the rounding value is 10).

so now either or both please help me understand the problem presented; why the soloution provided is NOT correct; and why the long way 'round the barn to get to an answer?






MichaelRed

 

[straybullet]

I have no idea

Ive set the reference and tried the following:

Function Ceiling(ByVal iOne As Double, ByVal iTwo As Double)
Ceiling = Excel.WorksheetFunction.ceiling(iOne, iTwo)
End Function

Still getting the Unidentified Function error.

I tried MichaelRed's function with the same result.

There is obviously something I am omitting...



Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

Upon starting fresh with MichaelRed's function, I am receiving a Syntax error message on

vCeiling = ((Int(dblX) + 1) * (dblSig * 10 ^ intPlaces)) * 1 / 10 ^

Let them hate - so long as they fear... Lucius Accius

 

[MichaelRed]

I tried MichaelRed's function

with MichaelRed's function

... but ... but but ... I supplied no function!

my post is only to state that you need a reference to the Excel Library in the app where you use it (presuming this should be MS Access, then showing a simplistic example of its use via the immediate / debug window.

Really, I am just asking the several why's ...

I'm truly confused as to the quagmire above.

The references to the "other" implementations (since a simple reference to the Excel Library permits you to use it in the same way / manner / arglist as you have ... ?)

The disclaimer re the math .. since the assertions appear to conflict with my (admittedly ancient and error prone) memory and the same exanple as returned by the excel funbction ... which is occassioned by the adition of the aforementioned reference.

then ... sudenly, i've provided a function? How so? Where?




MichaelRed

 

[straybullet]

Sorry - this whole mess has made me a wreck. I put the wrong name... M Blake wrote the function in question. I promise no more posting (specially using names!) without more coffee.

The reference directly to Excel (as ACTUALLY mentioned by MichaelRed), for some reason refused to work for me until this afternooon. Turned on the computer, opened the file to work on it some more, and NO ERRORS!!!

You can imagine how happy I was to see that!

As far as the result ending in 4, there is no telling how I managed that ...

I apologize for any misunderstanding I may have caused earlier.

Let them hate - so long as they fear... Lucius Accius

 

[ZmrAbdulla]

Just logged in...

so now either or both please help me understand the problem presented; why the soloution provided is NOT correct; and why the long way 'round the barn to get to an answer?

There was a mistake at my side. I was using the first code as if it is written for access without any reference to EXCEL.

Me.Text3 = ceiling(Me.Text1, Me.Text2)

this is the reason I was getting wrong answers.
after reading your post changed to something like this with added ref to EXCEL. It gives me correct answer.

Me.Text3 = Excel.WorksheetFunction.ceiling(Me.Text1, Me.Text2)

[COLOR=white red]I don't know what will be situation if a DB runs with runtime files where EXCEL not present.[/color]

a whisper only to (both of) you. This is the first time I am using this function, something I newly learned. I was very bad at Maths class.

Regards

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together