Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    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 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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    3 Star Lounger
    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;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!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Wksheet Function? (v2000)

    See <post#=301021>post 301021</post#> for a median function implemented in Access, with examples of use.

  5. #5
    3 Star Lounger
    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!

  6. #6
    3 Star Lounger
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  8. #8
    3 Star Lounger
    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  10. #10
    3 Star Lounger
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •