Distance between two points (Lat Lon) VBA

[VIC B]

I need to calculate the distance between two address. I have the LAT and LON on all of the addresses... there are tens of thousands of address. I need to do compares so I need to do this in VBA in ACCESS.
I have a formula that works in Excel, but it does not work in Access...

This is the Excel Formula...

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Lon1-Lon2))) *6371

Any Ideas ?

Thank you...

Victor

 

[gemma-the-husky]

that formula can easily be turned into a vba function, with a bit of re-writing,

you will end up with a function like this

function distance(LAT1, LON1, LAT2, LON2) AS DOUBLE
process the parameters according to your formula in here
end function

 

[VIC B]

I APPRECIATE THAT - I'm pretty good at automating databases, But Not Math... I don't know how to re-write the formula...
Everything I've seen so far is beyond my math understanding...
It's ashame the Excel Formula works perfectly...
But I have to have in in Access VBA...

Victor

 

[NigelShaw]

Hi

There is a db kicking around that does just what you are trying to achieve. I posted on to the thread so if you have no luck, search through my posts and you should find it.

Cheers

Nidge

 

[jdraw]

Vic B,

I responded to a similar request recently. It was driving distances between 2 zip codes
using Google Maps.
see http://www.access-programmers.co.uk/forums/showthread.php?t=225339 post #5

If you want to use vba and math see Pearsons work here.

Great Circle Distances In VBA
http://www.cpearson.com/Excel/latlong.aspx

 

[John Big Booty]

There is also a sample posted here at post #2 that allows you manage way points, calculate distances between way-points and view them in various online mapping apps.

 

[jdraw]

I APPRECIATE THAT - I'm pretty good at automating databases, But Not Math... I don't know how to re-write the formula...
Everything I've seen so far is beyond my math understanding...
It's ashame the Excel Formula works perfectly...
But I have to have in in Access VBA...

Victor

Vic,
I have set up some vba functions in Acc2003 using
a)Chip Pearson's vba code and
b) materials from http://www.zipcodeworld.com/samples/distance.vb.html.

Using the same start and end (Lat/Long) points, I compared the results of the 2 functions and found

Formula from ZipcodeWorld
Miles using Distance 1.38390935013792
Kilometers using Distance 2.22718620918837

Formula from CPearson
Miles using GreatCircle 1.38397013082645
Kilometers using GreatCircle 2.22728402622381

Let me know if you want to followup on this.

NOTE: This is a similar post to post #16 at
http://www.access-programmers.co.uk/forums/showthread.php?t=225339&page=2

Reason is that Vic answered in the other post and I responded there, but felt a response here was in order. The posts deal with Distances
driving distance between zip codes, and actual distance between lat/long points.

 

[NigelShaw]

Hi,

here is where i found information about this previously-

http://ezinearticles.com/?How-to-Bu...s-Into-Your-Access-Database-in-VBA&id=2408262

what you need on your form

text boxes

txtPostCodeStart
txtPostCodeEnd

( these are blank text boxes for entering your criteria. )

txtStartLat
txtEndLat
txtStartLong
txtEndLong

These are text boxes that are automatically populated through the"after update" event on the text boxes "txtPostCodeStart" and "txtPostCodeEnd" using simple RecordSet vba programming.

CODE-

Private Sub caldistance_Click()
 
Dim Distance As Double
On Error GoTo Err_caldistance_Click
 
If Me.TxtPostCodeStart = "" Then
MsgBox ("Please enter a Start Post Code")
Exit Sub
End If
 
If Me.TxtPostCodeEnd = "" Then
MsgBox ("Please enter an End Post Code")
Exit Sub
End If
 
Distance = (Sin((Me.TxtEndLat * 3.14159265358979) / 180)) * (Sin((Me.TxtStartLat * _
3.14159265358979) / 180)) + (Cos((Me.TxtEndLat * 3.14159265358979) / 180)) * _
((Cos((Me.TxtStartLat * 3.14159265358979) / 180))) * _
(Cos((Me.TxtStartLong - Me.TxtEndLong) * (3.14159265358979 / 180)))
 
Distance = 6371 * (Atn(-Distance / Sqr(-Distance * Distance + 1)) + 2 * Atn(1))
 
Me.TxTDistance = Distance
 
Exit_caldistance_Click:
Exit Sub
 
Err_caldistance_Click:
MsgBox Err.Description
Resume Exit_caldistance_Click
End Sub

i made a db with this to test it out and it worked ok. The only thing that is restrictive is that the distances ae calculated in direct straight lines or ( as the crow flies, a UK term )

if i find it, i'll post it but this should help you with the calcs


cheers

Nidge

 

[NigelShaw]

Found this in my stash. Not sure if it is the one i referred to earlier but Hey Ho lol


Cheers

Nidge

 

[jdraw]

Found this in my stash. Not sure if it is the one i referred to earlier but Hey Ho lol


Cheers

Nidge

Nidge, and others who may follow the thread,
The distance.mdb you attached deals specifically with Postal Codes - not Canadian Postal codes, probably UK. I didn't check for US.
There were no internal comments, so I dropped further activity.

Thanks for posting.