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