Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Long, Crystal
  New Posts New Posts RSS Feed - GetDistance function for Latitudes and Longitudes
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

GetDistance function for Latitudes and Longitudes

 Post Reply Post Reply
Crystal Long View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 33
Post Options Post Options   Thanks (0) Thanks(0)   Quote Crystal Long Quote  Post ReplyReply Direct Link To This Post Topic: GetDistance function for Latitudes and Longitudes
    Posted: 18 Aug 2012 at 10:51am
The shortest distance between 2 points is a straight line.  Here is a function to do that.  It does not take curvature or routes into account.

GetDistance Function for VBA

Function GetDistance(pLat1 As Double, pLng1 As Double _
   , pLat2 As Double, pLng2 As Double _
   , Optional pWhich As Integer = 1 _
   ) As Double
'12-13-08, 12-22
   ' calculates distance between 2 points of Latitude and Longitude
   ' in Statute Miles, Kilometers, or Nautical Miles
   ' crystal strive4peac2012 at
   ' pLat1 is Latitude of the first point in decimal degrees
   ' pLng1 is Longitude of the first point in decimal degrees
   ' pLat2 is Latitude of the second point in decimal degrees
   ' pLng2 is Longitude of the second point in decimal degrees
   On Error Resume Next
   Dim EarthRadius As Double
   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7
   Case 3:
      EarthRadius = 3437.74677
   Case Else
      EarthRadius = 3963
   End Select
   ' Radius of Earth:
   ' 1  3963.0 (statute miles)
   ' 2  6378.7 (kilometers)
   ' 3  3437.74677 (nautical miles)
   ' to convert degrees to radians, divide by 180/pi, which is 57.2958
   GetDistance = 0
   Dim X As Double
    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958))
   GetDistance = EarthRadius * Atn(Sqr(1 - X ^ 2) / X)
End Function

Related video tutorials:

Excel VLOOKUP to Calculate Distances using Latitude & Longitude (cc)
by Crystal for VLOOKUP Week

Convert VLOOKUP equations to Access - Distances (cc)
by Crystal  VLOOKUP Week

Feedback is appreciated, thank you

Warm Regards,

Microsoft MVP
remote programming and training

Access Basics by Crystal
Free 100-page book that covers essentials in Access

   (: have an awesome day :)

Warm Regards,
Remote Training & Programming - Access, Excel, word, PowerPoint, Camtasia, and more.
Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down