Parameter Query using zip code and distance to find records within radius and the distance to zipcode

I found this to help you calculate arccos...

http://www.eng-tips.com/viewthread.cfm?qid=65875

That's a start.. I'd have to think some more about how to proceed from here.

OK, try this...

Your prompt results go into a file with zipcode ceter and search radius (in miles). The query you'd create would use this table and the zipcode/lat & long table. Use the zipcode input and the zipcode in the field to calculate the distance, then use the radius as a criteria.

Thanks for the link, I've seen that formula on other answers here on EE, but I'm not sure how to implement it.

I tried adding "application.acos" to see if that would help, no luck. Here's exactly what I have so far, it's doesn't run because "it has an undefined function", also I haven't figured out how to add the radius prompt.


SELECT 3959*application.acos((Sin([startZip].[ZipCodeLatitude]/57.3)*Sin([endZip].[ZipCodeLatitude]/57.3))+(Cos([startZip].[ZipCodeLatitude]/57.3)*Cos([endZip].[ZipCodeLatitude]/57.3)*Cos(([startZip].[ZipCodeLongitude]-[endZip].[ZipCodeLongitude])/57.3))) AS exactDistance
FROM ZipCodes AS endZip, Zip_Codes AS startZip
WHERE (((endZip.ZipCodeZip)=[Enter Zip))
ORDER BY exactDistance;

I found this...

http://www.zipcodeworld.com/samples/distance.vb.html

The idea is you want to use the radius as a criteria in your query; that is, the distance calculated should be less than or equal to the distance entered.

so now we have answers... i come too late.

This is a module to compute distances "as the crow fies":

-----------------------------------------------------------------------
Option Compare Database
Option Explicit

Function ACos(X As Double) As Double
On Error Resume Next
    ACos = 0#
    ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

Function DistLatLon(LatA As Double, LonA As Double, LatB As Double, LonB As Double) As Double

    Const Radius = 6371 ' km or 3959 miles
    Const Deg2Rad = 3.14159265358979 / 180#
   
    Dim Angle As Double

    ' convert to radians:
    LatA = LatA * Deg2Rad
    LonA = LonA * Deg2Rad
    LatB = LatB * Deg2Rad
    LonB = LonB * Deg2Rad
   
    ' Cosine of the angle:
    Angle = ACos(Cos(LatA) * Cos(LatB) * Cos(LonB - LonA) + Sin(LatA) * Sin(LatB))
    DistLatLon = Angle * Radius

End Function
-----------------------------------------------------------------------

Then a readable version of arstark's query:

(for debugging:)

PARAMETERS [Enter Location:] Text, [Enter Max Distance:] Double;
SELECT B.*, DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]) AS Dist
FROM tblGeoLocations AS A, tblGeoLocations AS B
WHERE (((DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]))<=[Enter Max Distance:]) AND ((A.Name)=[Enter Location:]))
ORDER BY DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]);

(later:)
PARAMETERS [Enter Location:] Text, [Enter Max Distance:] Double;
SELECT B.*
FROM tblGeoLocations AS A, tblGeoLocations AS B
WHERE (((DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]))<=[Enter Max Distance:]) AND ((A.Name)=[Enter Location:]));

But the field and table names are a little different here :)

Ok, here's what I've got...

I created 2 modules and copy pasted Functions from harfang

Next I used the query you created and changed it to reflect my table:

PARAMETERS [Enter Location:] Text ( 255 ), [Enter Max Distance:] IEEEDouble;
SELECT B.*
FROM ZipCodes AS A, ZipCodes AS B
WHERE (((DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]))<=[Enter Max Distance:]) AND ((A.ZipcodeZip)=[Enter Location:]));

Seems like I'm almost there, however I'm getting a data type mismatch?

My linked table has:
ZipCodeZip as text
ZipCodeLatitude as number
ZipCodeLongitude as number

Can't see why I'm getting a mismatch?

Looks like I had some bad data(missing longitudes and latitudes) in the zip code table, the query is now running and looks to be returning right info.  
However, I still need to show the calculated distance in  the query results.

Here's my query so far...

PARAMETERS [Enter Location:] Text ( 255 ), [Enter Max Distance:] IEEEDouble;
SELECT B.*, EPMembers.FirstName, EPMembers.LastName
FROM ZipCodes AS A, ZipCodes AS B INNER JOIN EPMembers ON B.ZipCodeZip = EPMembers.Zip
WHERE (((DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]))<=[Enter Max Distance:]) AND ((A.ZipCodeZip)=[Enter Location:]) AND ((B.ZipCodeLatitude) Is Not Null) AND ((B.ZipCodeLongitude) Is Not Null));

I got it, I think...

PARAMETERS [Enter Location:] Text ( 255 ), [Enter Max Distance:] IEEEDouble;
SELECT B.*, EPMembers.FirstName, EPMembers.LastName, DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]) AS Distance
FROM ZipCodes AS A, ZipCodes AS B INNER JOIN EPMembers ON B.ZipCodeZip = EPMembers.Zip
WHERE (((DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]))<=[Enter Max Distance:]) AND ((A.ZipCodeZip)=[Enter Location:]) AND ((B.ZipCodeLatitude) Is Not Null) AND ((B.ZipCodeLongitude) Is Not Null))
ORDER BY DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]);

Harfang,
You were right, there were nulls in the table and I just didn't have the calculated distance showing in my results it was there.  

Does the query look ok to you?

Harfang,
Many thanks! Your answers did the trick and your time and help is greatly appreciated!
Allan