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).

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" wrote:

> 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