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?
8 11090
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. - '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
NeoPa 32,556
Expert Mod 16PB
Isn't that rather complicating matters? Wouldn't Pythagoras be an acceptable, and much simpler, approach? - 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.
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.
NeoPa 32,556
Expert Mod 16PB
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.
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.
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.
NeoPa 32,556
Expert Mod 16PB 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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jochen scheire |
last post by:
Is there a way I can calculate a field in a form based on another field in
the same form. When clicking submit, both values should be posted to the
next page. I want to be able to type in a value...
|
by: mahsa |
last post by:
Hi,i want to give aa zipcode and find the distance between 2 zipcodes do you have any idea?its better i
I can find some thing free but not necessar
tanck you
|
by: Kevin |
last post by:
Hi all,
Does anyone know of a script or package that will allow me to calculate
the localtime given a country code (and optional state/province for
US/Canada)? It should factor in daylight...
|
by: Golan |
last post by:
Hi all,
I need to write a code which gets a date (in format 'YYYYMMDDhh24MISS')
and number of seconds (X), and returns the date (again in format
'YYYYMMDDHH24MISS') that is X seconds prior to...
|
by: Pacific Fox |
last post by:
Hello all,
Does anyone know the SQL statement for calculating surrounding suburbs,
or can point me in the right direction?
I have a database of Australian postal codes and their centroids in...
|
by: Alan Johnson |
last post by:
24.1.1.3 says about InputIterators:
Algorithms on input iterators should never attempt to pass through the
same iterator twice. They should be single pass algorithms.
In 24.3.4.4 summarizes the...
|
by: FunkHouse9 |
last post by:
I am trying to set up a page that calculates a total ad price based on a word count value. I have the word count displaying properly in op3, but just can't get the price even close to working right....
|
by: Killer42 |
last post by:
Public Function DistanceBetween(ByVal X1 As Single, ByVal Y1 As Single, ByVal X2 As Single, ByVal Y2 As Single) As Single
' Calculate the distance between two points, given their X/Y coordinates....
|
by: sarah.kidd |
last post by:
Hello
I was wondering if someone could please help me.
I have created a google map:
http://www.cancerpatientgis.com/markers%20test/HospPat_markerstest.html
|
by: Breeves22 |
last post by:
Hi all,
I was wondering if it was possible to use Access 2000 to auto calculate a date in a field operating under a 5 day working week
Thankyou in advance
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |