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

>
Want a good read? Try The Summer of His Life (available on Amazon!)

GetDistance function for Latitudes and Longitudes

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


Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 35
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 road 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 yahoo.com
   ' http://www.rogersaccesslibrary.com/forum/topic604_post622.html
  
   'PARAMETERS
   ' 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
http://www.YouTube.com/watch?v=bc92JR2OPss

Convert VLOOKUP equations to Access - Distances (cc)
by Crystal  VLOOKUP Week
http://www.YouTube.com/watch?v=ED_Wd4w9PLk

Feedback is appreciated, thank you

Warm Regards,
Crystal

Microsoft MVP
Remote Training and Programming
Connect to me, let's build it together

Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

http://www.YouTube.com/LearnAccessByCrystal

 ~ have an awesome day ~
through sharing, we will all get better



Warm Regards,
Crystal
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together

http://www.AccessMVP.com/strive4peace
http://YouTube.com/LearnAccessByCrystal
~have an awesome day ~
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.