Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-23
11
Medium Priority
?
744 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:arstark
  • 5
  • 3
  • 3
11 Comments
 
LVL 2

Expert Comment

by:unicorn1824
ID: 13615100
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
 
LVL 2

Expert Comment

by:unicorn1824
ID: 13615136
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
 

Author Comment

by:arstark
ID: 13615316
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Expert Comment

by:unicorn1824
ID: 13615460
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
 
LVL 58

Expert Comment

by:harfang
ID: 13615559
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
 

Author Comment

by:arstark
ID: 13615931
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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 13616133
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
 

Author Comment

by:arstark
ID: 13616166
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
 

Author Comment

by:arstark
ID: 13616239
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
 
LVL 58

Assisted Solution

by:harfang
harfang earned 2000 total points
ID: 13616797
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
 

Author Comment

by:arstark
ID: 13616928
Harfang,
Many thanks! Your answers did the trick and your time and help is greatly appreciated!
Allan
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question