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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 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