Zip Code Distance calculations (1 Viewer)

Okay, I feel like an idiot. But I am not a programmer. I found the below VBA function, and I bet it will do exactly what I need it to do. But I'm not sure how to actually use it.

I want to use a Query to display the results. But I'm not sure how to do this with the code. Any help?

Private Const C_RADIUS_EARTH_KM As Double = 6371.1
Private Const C_RADIUS_EARTH_MI As Double = 3958.82
Private Const C_PI As Double = 3.14159265358979

Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
Latitude2 As Double, Longitude2 As Double, _
ValuesAsDecimalDegrees As Boolean, _
ResultAsMiles As Boolean) As Double

Dim Lat1 As Double
Dim Lat2 As Double
Dim Long1 As Double
Dim Long2 As Double
Dim X As Long
Dim Delta As Double

If ValuesAsDecimalDegrees = True Then
X = 1
Else
X = 24
End If

' convert to decimal degrees
Lat1 = Latitude1 * X
Long1 = Longitude1 * X
Lat2 = Latitude2 * X
Long2 = Longitude2 * X

' convert to radians: radians = (degrees/180) * PI
Lat1 = (Lat1 / 180) * C_PI
Lat2 = (Lat2 / 180) * C_PI
Long1 = (Long1 / 180) * C_PI
Long2 = (Long2 / 180) * C_PI

' get the central spherical angle
Delta = ((2 * ArcSin(Sqr((Sin((Lat1 - Lat2) / 2) ^ 2) + _
Cos(Lat1) * Cos(Lat2) * (Sin((Long1 - Long2) / 2) ^ 2)))))

If ResultAsMiles = True Then
GreatCircleDistance = Delta * C_RADIUS_EARTH_MI
Else
GreatCircleDistance = Delta * C_RADIUS_EARTH_KM
End If

End Function

Function ArcSin(X As Double) As Double
' VBA doesn't have an ArcSin function. Improvise.
ArcSin = Atn(X / Sqr(-X * X + 1))
End Function

that wont do zip code calculations

what it WILL do is take a pair of geographical latitiudes/longitudes, and evaluate the crows flies distance between them (actually a great earth circle distance taking into account the earths curvature)

you need to use google or mappoint to change a postcode INTO a lat/long

-------------
note that your formula returns the distance in NAUTICAL MILES - you need another constant to change this into normal miles

oops. looks like I missed saying some of my steps so far.

Thank you for replying.

I have a database set up that has the zipe codes and their longitude and latitude.

I now have these functions.But I am not sure how to call them in a query.

typically you would call this function for each row of a query, passing the lat and longs of the points in your table to the function

sort of

distance: greatcirclecalc(mylat, mylong)

but in a query it only really makes sense to evaluate distances for your points from an external fixed point - so here you are passing the lat and long for this point - but the query will need changing so that instead of having 4 arguments, it only has two, and the other two come from your fixed point.

hope that makes sense

it does make sense. thank you. But I guess I am not sure how you get the function to pull the lat and long I need? Is that just a variable that I need to declare before running the query?

Eventually Ill be needing this to find everything in a certain radius, which I understand is possible with this function as well. But first things first.

i dont quite understand exactly what you are doing ... but

your table of places will have fields for lat and long

so change this

Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
Latitude2 As Double, Longitude2 As Double, _
ValuesAsDecimalDegrees As Boolean, _
ResultAsMiles As Boolean) As Double

to this

const Latitude2 = whatever
const Longitude2 = whatever

[edited Feb 11 - forgot to delete the extra line]
Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
ValuesAsDecimalDegrees As Boolean, _
ResultAsMiles As Boolean) As Double

by taking out the red bit, and entering the blue bit

----------------------
now in your query add an extra column

distance: GreatCircleDistance(mylat,mylong,false,true)

where mylat/mylong are names of the latitude and logitude fields in your table, and the next two bits are true/false depending on how you want to see your result

(note - I think the result DOES take account of miles etc, and it is not in nautical miles as I suggested before) its just that a nautical mile IS 1 minute of arc of the earths circumference, and the formula uses spherical geometry to calculate the angle subtended (if thats the right word) by the two points

so the last thing is to replace the turquoise "whatevers" with the lat and long of the place you want to use, to compare with the places in your table - the base place

Obviously once you get it going, you can instead, use a form to select a place, and pick the lat and long up from that, instead of hard- coding it. THe main thing is to get used to using the function

Dave,

Thank you very much. It all finally clicked with your last post, and I was able to get it to do what I wanted it to do. I was just missing the "logic" of how it should work. Something you said made me see it.

Thank you very very much. It is now working exactly how I want it to.