Write a function "GetDistance" in a module in Access

Posted on 2003-03-18
Medium Priority
Last Modified: 2012-05-04
I would like to be able to write GetDistance(startLatitude, startLongitude, endLatitude, endLongitude)

GetDistance = 3959 * ACOS(
        ( SIN(startLatitude / 57.3) * SIN(endLatitude / 57.3))
        + (COS(startLatitude / 57.3)
        * COS(endLatitude / 57.3)
        * COS((startLongitude - endLongitude) / 57.3)))

Please write a function in a module that I can do that.
nTop = integer
startLatitude = double
startLongitude = double
endLatitude = double
endLongitude = double

In the end, I would like to be able to write a query ....

Select TOP 25 * where getDistance(lat, long, 23.4, 45.7) < 5 from Table
Question by:douglaskarr
  • 2
  • 2

Accepted Solution

MotelKamzoil earned 1000 total points
ID: 8160221
Um, the function wouldn't be hard to write, but you can't call a VBA function from inside SQL. If you want to stay in SQL (not use DAO), you'll need to write it out in full, like this:

    TOP 25
        3959 * ACOS(
            SIN(45 / 57.3) * SIN(lat / 57.3)
            COS(45 / 57.3) * COS(lat / 57.3)
             * COS(90 - long) / 57.3)
        ) < 5

This assumes you want to find 25 locations close to 45N90W.

If you want to use DAO (Data Access Objects), you can do something like this:

Private Sub foo()
    Dim locations As Recordset, distances As Recordset
    Set locations = CurrentDb.OpenRecordset("SELECT * FROM Locations")

    ' What you do here depends on your program's logic (are you trying to find
    ' the distance from one place to a bunch of places, or from each place to
    ' each other place)... I'll assume you want to find the distance from your
    ' house to each of Rome, London, Paris, etc.
    While Not locations.EOF
        distances!name = locations!name
        distances!distance = GetDistance(myHouseLat, _
                                         myHouseLong, _
                                         locations!lat, _
End Sub

' Get the distance between two points on the globe
Public Function GetDistance(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double)
    Dim a, b, c As Double
    a = Sin(lat1 * pi / 180) * Sin(lat2 * pi / 180)
    b = Cos(lat1 * pi / 180) * Cos(lat2 * pi / 180)
    c = Cos((long1 - long2) * pi / 180)
    GetDistance = 3959 * ArcCos(a + b * c)
End Function

' Inverse Cosine
Public Function ArcCos(x As Double)
    ArcCos = Atn(-x / Sqr(-x * x + 1)) + 2 * Atn(1)
End Function

Is this what you were looking for?
LVL 18

Expert Comment

ID: 8160671
Yes, you can call a function inside of VBA SQL!

Expert Comment

ID: 8160807
I'm sorry, I didn't know that. I'm used to working with MySQL/PHP, Access is kind of a new thing for me. Thank you very much, 1William!

I think my function is still valid, though. You just want to double-check the math.
LVL 18

Expert Comment

ID: 8160861
<looking the other way...>  Math..  Yikes!  

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question