Access Wksheet Function? (v2000)

Topic

New Reply

I am trying to convert an Excel workbook into Access. It is a straight-forward 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!

Reply | Quote

Replies

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.

Reply | Quote

That’s fantastic Hans!!!! Thanks a bunch. In fact, it had the exact function I was piecing together…GreatArcDistance.
I really appreciate it!

Reply | Quote

That’s fantastic Hans!!!! Thanks a bunch. In fact, it had the exact function I was piecing together…GreatArcDistance.
I really appreciate it!

Reply | Quote

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.

Reply | Quote

Ok…I found a way to pull Excel functions via code in: http://support.microsoft.com/default.aspx?…kb;en-us;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!!

Reply | Quote

See post 301021 for a median function implemented in Access, with examples of use.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!!

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

See post 301021 for a median function implemented in Access, with examples of use.

Reply | Quote

Ok…I found a way to pull Excel functions via code in: http://support.microsoft.com/default.aspx?…kb;en-us;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!!

Reply | Quote