Ceiling function in Access

  • Thread starter Thread starter Paul LeBlanc
  • Start date Start date
P

Paul LeBlanc

Last year a member named Geoff described how to call an Excel function in
Access.
I used the SQL statment
SELECT TblQSForecast.[INVENTORY DATE], VARIETY.ITEMCODE, VARIETY.VARNAME,
"10" & [TblQSForecast]![PLOT] AS PLOT, TblQSForecast.QTY,
TblQSForecast.[STICK DATE], (GetCeiling(([TblQSForecast]![QTY]*1.1),15)) AS
STICKQTY, TblRootingIdealSeq.[Rooting Ideal Seq]
FROM TblQSForecast INNER JOIN (TblRootingIdealSeq INNER JOIN VARIETY ON
TblRootingIdealSeq.ITEMCODE = VARIETY.ITEMCODE) ON TblQSForecast.VARIETY =
VARIETY.VARNAME
WHERE (((TblQSForecast.QTY)>0) AND ((TblQSForecast.[STICK DATE])=[Stick
date?]))
ORDER BY TblRootingIdealSeq.[Rooting Ideal Seq];
After setting up the VBA module as he described. It all works and returns
the values i expected but then gives an error about making an outgoing call
while making an asynchronus call
 
Here's the basis of Geoff post

You can call Excel's functions from Access.

In your Access database, set a reference to Excel (in the VBA Editor, Tools
References).
Click to expand...

Write your own GetCeiling() function in Access that calls the Excel
function, as follows (but see caveat at end):

Public Function GetCeiling(dblArg1 As Double, dblArg2 As Double) As Double

GetCeiling = _
Excel.Application.WorksheetFunction.Ceiling(dblArg1, dblArg2)

End Function

You can then call the GetCeiling() function in Access like you would call
the Ceiling function in Excel.


Paul LeBlanc said:
Last year a member named Geoff described how to call an Excel function in
Access.
I used the SQL statment
SELECT TblQSForecast.[INVENTORY DATE], VARIETY.ITEMCODE, VARIETY.VARNAME,
"10" & [TblQSForecast]![PLOT] AS PLOT, TblQSForecast.QTY,
TblQSForecast.[STICK DATE], (GetCeiling(([TblQSForecast]![QTY]*1.1),15)) AS
STICKQTY, TblRootingIdealSeq.[Rooting Ideal Seq]
FROM TblQSForecast INNER JOIN (TblRootingIdealSeq INNER JOIN VARIETY ON
TblRootingIdealSeq.ITEMCODE = VARIETY.ITEMCODE) ON TblQSForecast.VARIETY =
VARIETY.VARNAME
WHERE (((TblQSForecast.QTY)>0) AND ((TblQSForecast.[STICK DATE])=[Stick
date?]))
ORDER BY TblRootingIdealSeq.[Rooting Ideal Seq];
After setting up the VBA module as he described. It all works and returns
the values i expected but then gives an error about making an outgoing call
while making an asynchronus call
Click to expand...
 
Back
Top