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

Hi Experts,
I have a table of zipcodes with latitude and longitude and a table of members that I have complete addresses for including their zipcodes. I want to build a query that prompts for the zipcode (center point of my search) and the distance in miles (radius) for which I want to find all members within and calculate the distance for each member to the entered zipcode.

My tables are linked tables from SQL (not sure if that matters). I've seen answers here, but they all use functions that (arccos) that don't seem to be available in access 2000. I don't have the depth of knowledge that I should have with access (I'm a wizard based person in most cases) to figure out how to put the pieces together. Any help would be greatly appreciated!

Here's my tables:
The members table is EPMembers with the following pertinent fields:
FirstName
LastName
Zip

The zipcode table is ZipCodes with the following fields:
ZipCodezip
ZipCodeLongitude (e.g. data -67.139696000000001)
ZipCodeLatitude (e.g. data 18.288685000000001)

The query would prompt for [Enter zipcode] and then [Enter search radius in miles] and then would return a list of members and the distance they are from the center.

Thanks,
Allan
arstarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unicorn1824Commented:
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.
0
unicorn1824Commented:
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.
0
arstarkAuthor Commented:
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;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

unicorn1824Commented:
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.
0
harfangCommented:
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 :)
0
arstarkAuthor Commented:
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?
0
harfangCommented:
Perhaps you should start like this:

SELECT B.*, DistLatLon(0,0,[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]) As Dist
FROM ZipCodes AS B

Then:

PARAMETERS [Enter Location:] Text ( 255 );
SELECT B.*, DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]) As Dist
FROM ZipCodes AS A, ZipCodes AS B
WHERE (((A.ZipcodeZip)=[Enter Location:]));

and then and the sort order and criteria on the Dist column...

Another idea... make sure you have no Null values in the table... that can be a problem since the function does not accept Nulls.
If needed I can rewrite it to accept nulls.

Cheers!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arstarkAuthor Commented:
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));
0
arstarkAuthor Commented:
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?
0
harfangCommented:
Looks ok.

Come to think of it, one should never design a VB function used in queries that does not accept Null values... So here is an updated version, with Null accepted as entry:

Function DistLatLon(LatA, LonA, LatB, LonB)

    Const Radius = 6371 ' km or 3959 miles
    Const Deg2Rad = 3.14159265358979 / 180#
   
    Dim Angle As Variant
   
On Error GoTo DistLatLon_Error
   
    ' 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
    Exit Function
   
DistLatLon_Error:
    DistLatLon = Null
   
End Function

The only problem is that JetEngine will not know that the result is a number. So it is best to use it always as:

     DistLatLon( ....... ) + 0

The +0 will "explain" to JetEngine that the column will be a number (for formatting and such).

Good Luck
0
arstarkAuthor Commented:
Harfang,
Many thanks! Your answers did the trick and your time and help is greatly appreciated!
Allan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.