454,967 Members | 1,179 Online Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

# how to calculate distance based from lat long

 P: 3 I have a distance formula that works in excel but does not work on msaccess because some of the functions are not available like ACOS. dist= Acos(Sin(lat1)*Sin(lat2)+Cos(lat1)*Cos(lat2)*Cos(l ong2-long1))*6371 What formula can i use to replace this and make it work on ms access? Nov 10 '11 #1
8 Replies

 Expert 5K+ P: 8,679 You can actually make the existing Formula work within Access. Sin() and Cos() are supported whereas ACos() is not, but you can execute this Function within Access using Automation Code. The following Code will work, assuming you substitute Values for the actual Variables, of course. Expand|Select|Wrap|Line Numbers 'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library Dim objExcel As Excel.Application   Set objExcel = CreateObject("Excel.Application")   With objExcel.Application.WorksheetFunction   dist = .Acos(Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(long3 - long1)) * 6371 End With   MsgBox dist   objExcel.Quit Set objExcel = Nothing Nov 10 '11 #2

 Expert Mod 15k+ P: 31,709 Isn't that rather complicating matters? Wouldn't Pythagoras be an acceptable, and much simpler, approach? Expand|Select|Wrap|Line Numbers Dist = Sqr((Lat2-Lat1)^2+(Long2-Long1)^2) Euclidean geometry teaches us many things and gives all these tools, but we still need to select the simplest one. Nov 11 '11 #3

 Expert 5K+ P: 8,679 Wouldn't Pythagoras be an acceptable, and much simpler, approach? I don't think that we are talking about the Pythagorean Theory (Hypotenuse) here are we? The 2 Expressions are not equivalent, are they? P.S. - The Formula posted by the OP will calculate the Distance between 2 Latitudes/Longitudes. Nov 11 '11 #4

 Expert Mod 15k+ P: 31,709 I suppose I've never dealt with them in detail. They must be angle co-ordinates rather than distance ones then. Surely, even in that case, a mean (or otherwise constant) value for the radius of the Earth will always introduce a fair amount of inaccuracy, no? I'm assuming that's the significance of the 6,371 value in the formula. PS. Understanding that the Earth is not quite spheroid led me to the conclusion that absolute accuracy was not an attainable aim - hence my equally imprecise suggestion based on an approximation. Clearly it would need different parameters from what's available though. Nov 11 '11 #5

 P: 3 Phytagoras should be ok as i don't need the exact distance rather the nearest to a certain point. Also, sqr is also not in ms access 2007 @ADezii How can i write that syntax on MS Access?...What i have is a excel spread sheet with column of lat1, long1, lat2, long2 and would like to output a table with additional column with distance within access. Nov 12 '11 #6

 P: 3 I guess my issues is how i could make sqr or acos or other functions in excel for that matter available on ms access 2007. Nov 12 '11 #7

 Expert Mod 15k+ P: 31,709 Sqr() actually is available (See Access 2007 VBA Programming For Dummies) to all of Office 2007 via the VBA library (in the class Math). For Excel worksheet functions I believe one would use SQRT() instead though. Nov 12 '11 #8

 Expert 5K+ P: 8,679 How can i write that syntax on MS Access?...What i have is a excel spread sheet with column of lat1, long1, lat2, long2 and would like to output a table with additional column with distance within access. This can easily be done via a Query, but I must first know how are you accessing the Data in the Excel Spreadsheet, Linked, Automation Code, etc.? I also need to know the specific Data Type for Longitude and Latitude. Kindly post some Sample Data for these Values as it wouold exist in the Spreadsheet. Nov 12 '11 #9 