Results 1 to 3 of 3
  1. #1
    KevinB is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    1

    Calculate Miles in Access Query Using Latitude/Longitude

    Hello,

    New to the forum, but I thought I'd post something that might be helpful to someone out there.

    I needed a way to calculate mileage in Access using Latitude/Longitude. I didn't want to connect to the Excel Object Library to use formulas or involve any complicated SQL functions....i just wanted a formula. I couldn't find anything concrete online, but I was able to combine a few peoples methods into one formula and it works fine.

    The formula requires the Lat/Long to be represented as Radian (which is the formula of (Coordinate*(PI/180))....or Coordinate*0.0174532925199433). For the below formula, i have already converted to Radians.



    Where:
    OLatRadian = Origin Latitude Radian
    DLatRadian = Destination Latitude Radian
    OLonRadian = Origin Longitude Radian
    DLongRadian = Destination Longitude Radian

    Then use this formula:

    ((((Atn((-1*((Cos([OLatRadian])*Cos([DLatRadian])*Cos((-1*[DLongRadian])-(-1*[OLongRadian])))+(Sin([OLatRadian])*Sin([DLatRadian]))))/Sqr((-1*((Cos([OLatRadian])*Cos([DLatRadian])*Cos((-1*[DLongRadian])-(-1*[OLongRadian])))+(Sin([OLatRadian])*Sin([DLatRadian]))))*((Cos([OLatRadian])*Cos([DLatRadian])*Cos((-1*[DLongRadian])-(-1*[OLongRadian])))+(Sin([OLatRadian])*Sin([DLatRadian])))+1))+2*Atn(1))*3959))))

    3959
    - This is for miles. You can change this to KM, feet, or whatever other unit you want.

    It should be noted, this is straight line mileage, not driving distance!

    Just thought it would help someone out,

    Kevin

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Nice, thanks.

    I presume the Lat and Long values are decimal degrees in a number type field, not degrees/minutes/seconds in a text field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715

Please reply to this thread with any new information or opinions.

Similar Threads

  1. latitude and longitude w Google
    By wnicole in forum Access
    Replies: 2
    Last Post: 03-26-2014, 10:58 AM
  2. Add Total Miles
    By Darth21 in forum Queries
    Replies: 1
    Last Post: 03-25-2014, 09:16 AM
  3. How can I get latitude and longitude
    By w0st in forum Access
    Replies: 1
    Last Post: 01-05-2014, 05:28 PM
  4. Replies: 1
    Last Post: 03-04-2012, 11:22 AM
  5. Calculate Miles
    By rbiggs in forum Access
    Replies: 4
    Last Post: 06-30-2011, 01:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums