excel functions in access queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi all -
I currently use an Excel worksheet function that allows me to take a value,
write an IF statement against that value that outputs a new value using the
ceiling/floor MS Excel worksheet function. IF(CEILING(#val,500) - #val)>390,
FLOOR(#val,500), CEILING(#val,500)) Basically, if my #val = 1700, the
equation would result in a new value of 2000 using the above. The only
function in an Access query I see is ROUND or ROUNDTONEAREST - any help is
appreciated.
 
Lisa C said:
hi all -
I currently use an Excel worksheet function that allows me to take a
value,
write an IF statement against that value that outputs a new value using
the
ceiling/floor MS Excel worksheet function. IF(CEILING(#val,500) -
#val)>390,
FLOOR(#val,500), CEILING(#val,500)) Basically, if my #val = 1700, the
equation would result in a new value of 2000 using the above. The only
function in an Access query I see is ROUND or ROUNDTONEAREST - any help is
appreciated.
Click to expand...

Lisa,

In the VBA window you can add a reference to the Excel object library. You
can then create a function that returns the value of the Excel worksheet
function you want to use. For example:

Public Function ExcelTest(ByVal lngValue As Long) As Long
Dim retval As Long

If Excel.WorksheetFunction.Ceiling(lngValue, 500) - lngValue > 390 Then
retval = Excel.WorksheetFunction.Floor(lngValue, 500)
Else
ExcelTest = Excel.WorksheetFunction.Ceiling(lngValue, 500)
End If

ExcelTest = retval
End Function

This function can be called from anywhere in your application, inclusing
queries.

Ed Metcalfe.
 
Back
Top