Results 1 to 10 of 10
Thread: Access Wksheet Function? (v2000)

20041201, 01:36 #1
 Join Date
 Dec 2001
 Location
 Royal Oak, Michigan, USA
 Posts
 255
 Thanks
 0
 Thanked 0 Times in 0 Posts
Access Wksheet Function? (v2000)
I am trying to convert an Excel workbook into Access. It is a straightforward conversion with one exception. I am using some advanced functions in Excel that I can't load into Access. For instance, Arcatangent...ATAN() in XL and PI() are two that are not available in Access. How can I add Functions to Access? They are available in XL so it seems I should be able to leverage them in Access.
Cheers!

20041201, 01:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Access Wksheet Function? (v2000)
You could set a reference to the Excel object library and create an Excel application object in order to be able to use Excel functions, but it is a lot of overhead.
Atn is the general VBA equivalent of ATAN, and Pi can be computed as Pi = Atn(1) * 4.
If you need more functions, check out ACC97: Neatcd97.mdb Available in Download Center. Although designed for Access 97, it is useful in later versions of Access too.

20041201, 01:48 #3
 Join Date
 Dec 2001
 Location
 Royal Oak, Michigan, USA
 Posts
 255
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Access Wksheet Function? (v2000)
Ok...I found a way to pull Excel functions via code in: http://support.microsoft.com/default.aspx?...kb;enus;198571
For instance, if I want to reference the Median() function in Excel, they illustrate the following code:
>>>>>>>>>>>>>>>>
Sub xlMedian()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
MsgBox objExcel.Application.Median(1, 2, 5, 8, 12, 13)
objExcel.Quit
Set objExcel = Nothing
End Sub
>>>>>>>>>>>>>>>>
However, I need to reference this function in a query. For illustrative sake, I want to find the median of [JohnsSales] and [SuesSales], how would I do this?
Thanks!!

20041201, 01:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Access Wksheet Function? (v2000)
See <post#=301021>post 301021</post#> for a median function implemented in Access, with examples of use.

20041201, 01:55 #5
 Join Date
 Dec 2001
 Location
 Royal Oak, Michigan, USA
 Posts
 255
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Access Wksheet Function? (v2000)
That's fantastic Hans!!!! Thanks a bunch. In fact, it had the exact function I was piecing together...GreatArcDistance.
I really appreciate it!

20041201, 03:04 #6
 Join Date
 Dec 2001
 Location
 Royal Oak, Michigan, USA
 Posts
 255
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Access Wksheet Function? (v2000)
Hans,
I worked hard not to bug you but I hit a snag. When I calculate using the GreatArcDistance() function, I have two small issues. What do I use as the radius (I am assuming 6,378.8 kilometers because that is the assumed radius of the earth). Finally, in what units is the final answer? It is too large to be miles or meters. I know you don't have all the answers but do you know of a document that goes into more details on these calculations?
Thanks!
Dashiell

20041201, 09:24 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Access Wksheet Function? (v2000)
I hadn't studied the sample database closely. It seems to be buggy: both the ArcCos and GreatArcDistance functions are incorrect. Here are hopefully correct versions (you still need other functions from the sample database:
Function ArcCos(x As Double) As Double
' Inverse Cosine
If x = 1 Then
ArcCos = 0
ElseIf x = 1 Then
ArcCos = Pi
Else
ArcCos = Pi / 2  Atn(x / Sqr(x * x + 1))
End If
End Function
Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double, Radius As Double) As Double
' Calculates the Great Arc (shortest) distance between 2 locations on the globe.
' Uses functions from Trigonometry
Dim X1 As Double
Dim Y1 As Double
Dim Z1 As Double
Dim X2 As Double
Dim Y2 As Double
Dim Z2 As Double
Dim CosX As Double
Dim ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1  X2) * (X1  X2) + (Y1  Y2) * (Y1  Y2) + (Z1  Z2) * (Z1  Z2))
CosX = 1  ChordLen * ChordLen / (2 * Radius * Radius)
GreatArcDistance = ArcCos(CosX) * Radius
End Function

20041201, 14:51 #8
 Join Date
 Dec 2001
 Location
 Royal Oak, Michigan, USA
 Posts
 255
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Access Wksheet Function? (v2000)
Hans,
That gets me much closer. Here are the zip codes I'm using as tests as well as their XY coordinates:
Zip1 Lat1 Lon1 Zip2 Lat2 Lon2 GreatArcAnswer
48072 42.498463 83.185364 48111 42.18206 83.485329 43.013401991036
The calculation I am using in the query is as follows:
GreatArcAnswer: GreatArcDistance([Lat1],[Lon1],[Lat2],[Lon2],6378.8)
where 6378.8 is the earth's approximate radius in kilometers. The answer should be closer to 26 miles but the answer is coming up as 43.0134. I'm not certain if the radius is correct or what the 43.0134 actually represents. If you have any more insight, I would appreciate it. Please don't spend any more time on it if you don't know straight away.
Thanks!
Andrew

20041201, 15:07 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Access Wksheet Function? (v2000)
If you enter the radius of the earth in kilometers, the function returns a result in kilometers. 43.01 km is roughly equivalent to 26.75 miles.

20041201, 15:10 #10
 Join Date
 Dec 2001
 Location
 Royal Oak, Michigan, USA
 Posts
 255
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Access Wksheet Function? (v2000)
That's why you have Moderator WMVP in front of your name as opposed to 2StarLounger. Thanks for everything and have a great day...you eased a heavy burden and I really appreciate it!!