Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 17th, 2008, 11:52 PM
Registered User
 
Join Date: Jul 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Wanting to call excel function for quartile in acc

Hello,
I thank you in advance for your time and expertise. I am working on a project for a non-profit and am hoping to get some help in getting the quartile function that is in Excel and use it in Access. I have played with getting other functions working from excel, but when I try to use any formula that requires an array, I can't get it working.Unfortunately they have Access 2000 and I am not a very experienced programmer. I have one data_table, the field is data. I found this code on the web, but can't get it working. I keep getting an error "cannot find worksheet function". I added the reference to excel through tools, but still nothing.

Thank you for any help, advice or good thoughts.

Public Function Percentile(strTbl As String, strFld As String, k As Double)
As Double
Dim rst As ADODB.Recordset
Dim dblData( ) As Double
Figure 7-11. Calling the FV function from a form
Figure 7-12. Message box displayed from the cmdFV_Click event
200 | Chapter 7, External Programs and Data
#61 Use Excel Functions Inside Access
HACK
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set rst = New ADODB.Recordset
rst.Open "Select * from " & strTbl, CurrentProject.Connection,
adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(strFld)
rst.MoveNext
Next x
Percentile = xl.WorksheetFunction.Percentile(dblData, k)
rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

Private Sub cmdPercentile_Click( )
Dim dblPercentile As Double
dblPercentile = Percentile("tblData", "SampleData", txtK)
MsgBox "Percentile = " & dblPercentile, vbInformation, "Percentile"
End Sub

 
Old July 22nd, 2008, 07:25 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   The code you are using seems kind of strange. I am surprised that you are only getting one error since the syntax indicates you would be getting many more.

   Can you find the math for the operation you wish to carry out, and then just put that in your code? Use a module and call it when needed, that way you can reuse the code.




mmcdonal

Look it up at: http://wrox.books24x7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Call to function Awkuzu BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 1 September 19th, 2007 06:13 PM
Call C++ function from C#.NET ashooagarwal .NET Framework 1.x 0 May 24th, 2007 10:47 PM
Call a Function stealthdevil Access VBA 2 November 2nd, 2006 10:38 AM
How to call javascript function from VB function vinod_yadav1919 VB How-To 0 February 13th, 2006 06:03 AM
Possible to Create Acc 97 mdb from Acc 2000?? timmaher Access VBA 2 April 21st, 2005 04:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.